Java使用JDBC操作Mysql資料庫增删改查

生成JDBCUtils工具類

import java.sql.*;

public class JDBCUtils {

	// 驅動
    String driver = "com.mysql.jdbc.Driver";
    // 連接配接網址端口
    String url= "jdbc:mysql://hadoop102:3306/test?useUnicode=true&characterEncoding=UTF8&useSSL=false";
    String user = "root";
    String password = "123456";

    public Connection conn;

    public JDBCUtils() {

        try {
            Class.forName(driver);// 加載驅動程式
            conn = (Connection) DriverManager.getConnection(url, user, password);// 連續資料庫

            if(!conn.isClosed())
                System.out.println("Succeeded connecting to the Database!");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
	// 關閉資源
    public void close(Connection conn,PreparedStatement preStmt,Statement statement,ResultSet rs) {
        try {
            if(conn!=null){
                conn.close();
            }

            if (preStmt!=null){
                preStmt.close();
            }

            if (statement!=null){
                statement.close();
            }
            if (rs!=null){
                rs.close();
            }
        } catch (Exception e) {
            System.err.println("-----------------  資源關閉失敗！ -----------------");
            e.printStackTrace();
        }
    }
}
           

建立增删改查類

import radomlogTxt.com.pb.utils.JDBCUtils;

import java.sql.*;

public class JavaMySQLJdbcServices {

    JDBCUtils db = new JDBCUtils();
    PreparedStatement preStmt;
    Statement statement;
    ResultSet rs;

    public static void main(String[] args){

        JavaMySQLJdbcServices javaMySQLJdbcServices = new JavaMySQLJdbcServices();

        // javaMySQLJdbcServices.add("student","6","LRF","F");
        // javaMySQLJdbcServices.update("student","6","name='JN'","gender='M'");
        javaMySQLJdbcServices.del("student","4");
        javaMySQLJdbcServices.show("student","id","name","gender");

        // 關閉資源
        javaMySQLJdbcServices.db.close(javaMySQLJdbcServices.db.conn,
                javaMySQLJdbcServices.preStmt,
                javaMySQLJdbcServices.statement,
                javaMySQLJdbcServices.rs);
    }



    //插入操作
    /**
    * tableName 表名
    * args 增添的字段，（所有字段）
    */
    public  int add(String tableName,String  ... args) {

        int i = 0;

        String value = "(";
        for (int j = 0; j < args.length; j++) {
            if (j == args.length - 1) {
                value = value + "'" + args[j] + "'" + ")";
                break;
            }
            value = value + "'" + args[j] + "'" + ",";
        }

        String sql="insert into "+ tableName +" values " + value;

        try {
            preStmt = (PreparedStatement) db.conn.prepareStatement(sql);
            /*preStmt.setString(1, uname);
            preStmt.setString(2, uemail);
            preStmt.setString(3, upwd);*/
            preStmt.executeUpdate();
            //Statement statement = (Statement) db.conn.createStatement();
            //statement.executeUpdate(sql);
        } catch (Exception e) {
            System.err.println("--------- 插入失敗 ---------");
            e.printStackTrace();
        }

        //傳回影響的行數，1為執行成功
        return i;
    }

    //查找操作
    /**
    * tableName 表名
    * num增添的字段，（所有字段）
    */
    public  void show(String tableName,String ... num){

        String sql ="select * from " + tableName;
        System.out.println("---------------------------------------------------");
        try {
            statement = (Statement) db.conn.createStatement();
            rs = (ResultSet) statement.executeQuery(sql);

            while(rs.next()){
                String out = "";
                for (int i = 0; i < num.length; i++) {
                    out += (rs.getString(num[i]) + "\t");
                }
                System.out.println(out);
            }
        } catch (SQLException e) {

            System.err.println("--------- 查詢失敗 ---------");
            e.printStackTrace();
        }
    }

    //更新操作
    /**
    * tableName 表名
    * id 序号，編号
    * args 增添的字段，（修改的字段） 例如： "name='LiShi'"
    */
    public  int update(String tableName,String id,String ... args) {

        int i =0;
        String value = "";
        for (int j = 0; j < args.length; j++) {
            if (j == args.length - 1) {
                value +=args[j];
                break;
            }
            value += args[j] + ",";
        }
        String sql="update "+ tableName +" set "+ value +" where id=" + id;


        try {

            preStmt = (PreparedStatement) db.conn.prepareStatement(sql);
            preStmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        //傳回影響的行數，1為執行成功
        return i;
    }

    //删除操作
    /**
    * tableName 表名
    * id 序号，編号
    */
    public int del(String tableName,String id) {
        int i=0;
        String sql="delete from "+ tableName +" where id=" + id;
        try {

            preStmt = (PreparedStatement) db.conn.prepareStatement(sql);
            preStmt.executeUpdate();
        } catch (SQLException e){
            e.printStackTrace();
        }

        return i;//傳回影響的行數，1為執行成功
    }

}