原生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(); }