JDBC 简单的增删改查 MySQL 数据库_jdbc mysql 增删改查-优快云博客

本文介绍了一个简单的Java应用程序如何使用JDBC进行数据库操作，包括插入、删除、更新及查询用户信息。通过具体的代码示例展示了如何建立数据库连接、执行SQL语句以及处理结果集。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

// 实体类 User 对应 user 表，有三个字段 id，username，password
public class User {
	// id
	private Integer id;

	// 用户名
	private String username;

	// 密码
	private String password;

	public Integer getId() {
		return id;
	}

	public void setId( Integer id ) {
		this.id = id;
	}

	public String getUsername() {
		return username;
	}

	public void setUsername( String username ) {
		this.username = username;
	}

	public String getPassword() {
		return password;
	}

        public void setPassword( String password ) {
		this.password = password;
	}

}

一般 jdbc 操作要引用这些类：

import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;

获得数据库连接

// getConn
private static Connection getConn() {
    String driver = "com.mysql.jdbc.Driver";
    String url = "jdbc:mysql://localhost:3306/testDB";
    String username = "root";
    String password = "";
    Connection conn = null;

    try {
        Class.forName( driver ); //classLoader, 加载对应驱动
        conn = ( Connection ) DriverManager.getConnection( url, username, password );
    } catch ( ClassNotFoundException e ) {
        e.printStackTrace();
    } catch ( SQLException e ) {
        e.printStackTrace();
    }
    return conn;
}

关闭数据库连接

	// 关闭 PreparedStatement
	public static void closeThem( PreparedStatement pstmt ) {
		try {
			if ( pstmt != null ) {
				  pstmt.close();
			}
		} catch( Exception e ) {
			e.printStackTrace();
		}
	}

	// 关闭 Connection
	public static void closeThem( Connection conn ) {
		try{
			if ( conn != null ) {
				 conn.close();
			}
		} catch ( Exception e ) {
			e.printStackTrace();
		}
	}

插入一个对象

//
private static int insert( User user ) {
    Connection conn = getConn();    
    PreparedStatement pstmt = null;

    String sql = "insert into user( id, username, password ) values( ?, ?, ? )";

    int i = 0;   
    
    try{
        pstmt = ( PreparedStatement ) conn.prepareStatement( sql );
        pstmt.setString( 1, user.getId() );
        pstmt.setString( 2, user.getUsername() );
        pstmt.setString( 3, user.getPassword() );

        i = pstmt.executeUpdate();
    } catch( SQLException e ){
        e.printStackTrace();
    } finally {
 	closeThem( pstmt );
	closeThem( conn );
    }
    return i;
}

删除一个对象

private static int delete( String username ) {
    Connection conn = getConn();
    PreparedStatement pstmt = null;

    int i = 0;
    String sql = "delete from user where username ='" + username + "'";    

    try{
        pstmt = ( PreparedStatement ) conn.prepareStatement( sql );
        i = pstmt.executeUpdate();
        System.out.println( "result: " + i );
       
    } catch( SQLException e ) {
        e.printStackTrace();
    } finally {
	closeThem( pstmt );
	closeThem( conn );
    }
    return i;
}

修改一条记录

private static int update( User user ) {
    Connection conn = getConn();
    PreparedStatement pstmt = null;
   
    int i = 0;

    String sql = "update user set username = '" + "testIt" + "' where username ='" + user.getUsername() + "'";
   
    try {
        pstmt = ( PreparedStatement )conn.prepareStatement( sql );
        i = pstmt.executeUpdate();

        System.out.println( "result: " + i );
    } catch ( SQLException e ) {
        e.printStackTrace();
    } finally {
	closeThem( pstmt );
	closeThem( conn );
    }

    return i;
}

查询全部记录

private static Integer getAll() {
    Connection conn = getConn();
    PreparedStatement pstmt = null;

    String sql = "select * from user";

    try {
        pstmt = ( PreparedStatement )conn.prepareStatement( sql );
        ResultSet rs = pstmt.executeQuery();

        int col = rs.getMetaData().getColumnCount();

        System.out.println( "=====" );

        while ( rs.next() ) {
            for ( int i = 1; i <= col; i++ ) {
                System.out.print( rs.getString( i ) + " ");
                if ( ( i == 2 ) && ( rs.getString( i ).length() < 8 ) ) {
                    System.out.print( " " );
                }
             }
            System.out.println();
        }

        System.out.println( "=====" );
    } catch ( SQLException e ) {
        e.printStackTrace();
    } finally {
	closeThem( pstmt );
	closeThem( conn );
    }

    return null;
}