原生java連接mysql還是比較麻煩的,所以我們還是很有必要把連接數據庫及操作數據庫表的方法進行封裝,這樣就會容易很多
這里做一個簡單的封裝,方便大家學習使用
public class ConnDB { // MySQL 8.0 以下版本 - JDBC 驅動名及數據庫 URL //static final String DRIVER = "com.mysql.jdbc.Driver"; //static final String DB_URL = "jdbc:mysql://localhost:3306/info0918"; // MySQL 8.0 以上版本 - JDBC 驅動名及數據庫 URL static final String DRIVER = "com.mysql.cj.jdbc.Driver"; static final String DB_URL = "jdbc:mysql://localhost:3306/ncycc?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC"; static final String USER = "root"; static final String PASS = "root"; private Connection conn = null; private PreparedStatement ps = null; private ResultSet rs = null; public ConnDB(){ try { Class.forName(DRIVER); //將mysql驅動注冊到DriverManager中去 conn = DriverManager.getConnection(DB_URL,USER,PASS); } catch (SQLException | ClassNotFoundException e) { e.printStackTrace(); } } //基本的增,刪,改操作 public int exec(String sql, Object...args) { int count = 0; try { //prepareStatement對象防止sql注入的方式是把用戶非法輸入的單引號用\反斜杠做了轉義,從而達到了防止sql注入的目的 ps = conn.prepareStatement(sql); //設置占位符參數 if(args != null) { for(int i = 0; i < args.length; i++) { ps.setObject(i+1, args[i]); } } count = ps.executeUpdate(); //設置占位符參數 } catch(SQLException e) { System.out.println(e.getMessage()); } return count; } //返回新增加數據的id public int lastInsertId(String sql, Object...args) { int id = -1; try { //prepareStatement對象防止sql注入的方式是把用戶非法輸入的單引號用\反斜杠做了轉義,從而達到了防止sql注入的目的 ps = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS); //設置占位符參數 if(args != null) { for(int i = 0; i < args.length; i++) { ps.setObject(i+1, args[i]); } } ps.executeUpdate(); rs = ps.getGeneratedKeys(); if(rs.next()) { id = rs.getInt(1); } //設置占位符參數 } catch(SQLException e) { System.out.println(e.getMessage()); } return id; } //查詢操作 public ResultSet fetch(String sql, Object...args) { try { ps = conn.prepareStatement(sql); //設置占位符參數 if(args != null) { for(int i = 0; i < args.length; i++) { ps.setObject(i+1, args[i]); } } rs = ps.executeQuery(); //返回 查詢的數據結果 //設置占位符參數 } catch(SQLException e) { System.out.println(e.getMessage()); } return rs; } public void close() { try { if(rs != null) {rs.close(); rs = null;} if(ps != null) {ps.close(); ps = null;} if(conn != null) {conn.close(); conn = null;} } catch(SQLException e) { System.out.println(e.getMessage()); } } }
使用方法如下:
ConnDB conn = new ConnDB(); String sql = "update webconfig set post = ? where id = ?"; int num = conn.exec(sql,"88888886",1); sql = "select * from webconfig where id = ?"; ResultSet rs = conn.fetch(sql,1); try{ if(rs.next()){ System.out.println(rs.getString("address")); } } catch (SQLException e){ System.out.println(e.getMessage()); } finally { conn.close(); }