先在mysql中新建一个表
如图,表名为friends,数据库名为demo。
封装一个JDBCUtil工具类
/** * JDBCUtil工具类 */ public class JDBCUtil { private static final String driver = "com.mysql.cj.jdbc.Driver"; //?前面的demo要改为自己需要的数据库名 private static final String url = "jdbc:mysql://localhost:3306/demo? useSSL=false&serverTimezone=UTC&useUnicode=true&characterEncoding=utf8"; //mysql用户名 private static final String userName = "root"; //mysql登录密码 private static final String password = "200297"; // 获取数据库连接 public static java.sql.Connection getConnection() { java.sql.Connection con = null; try { Class.forName(driver); con = DriverManager.getConnection(url, userName, password); } catch (ClassNotFoundException | SQLException e) { e.printStackTrace(); } return con; } // 数据库查询,返回结果集 public static ResultSet query(Connection con, PreparedStatement st, ResultSet rs, String sql , Object[] params) throws SQLException { st = con.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY); if (params != null) { for (int i = 0; i < params.length; i++) { st.setObject(i + 1, params[i]); } } rs = st.executeQuery(); return rs; } // 数据库增删改 public static int update(Connection con, String sql , Object[] params, ResultSet rs, PreparedStatement st) throws SQLException { st = con.prepareStatement(sql); for (int i = 0; i < params.length; i++) { st.setObject(i + 1, params[i]); } return st.executeUpdate(); } // 关闭数据库连接 public static void release(Connection con, Statement st, ResultSet rs) { boolean flag = true; if (rs != null) { try { rs.close(); rs = null; } catch (SQLException e) { e.printStackTrace(); flag = false; } } if (st != null) { try { st.close(); st = null; } catch (SQLException e) { e.printStackTrace(); flag = false; } } if (con != null) { try { con.close(); con = null; } catch (SQLException e) { e.printStackTrace(); flag = false; } } } }
使用数据库操作工具类完成增删改查
JDBCUtil 中工具分别为: getConnection获取数据库的连接、 query数据库的查询、 update数据库的增 删改、 release关闭数据库连接。
1.查询数据
public class Test2 { public static void main(String[] args) { Connection con = null; PreparedStatement st = null; ResultSet rs = null; try { con=JDBCUtil.getConnection(); String sql = "SELECT * FROM friends WHERE id = ? AND name = ?"; st = con.prepareStatement(sql); Object[] params = {2, "ljp"}; rs = JDBCUtil.query(con, st, rs, sql, params); while (rs.next()) { int id = rs.getInt(1); String name = rs.getString(2); System.out.println(id + " " + name + " " ); } } catch(SQLException e) { e.printStackTrace(); } finally { // 关闭数据库在finally里面 JDBCUtil.release(con, st, rs); } } }
运行结果:
2.修改数据
public class Test3 { public static void main(String[] args) { Connection con = null; PreparedStatement st = null; ResultSet rs = null; try { con = JDBCUtil.getConnection(); String sql = "UPDATE friends SET id= ? WHERE name = ?"; st = con.prepareStatement(sql); //修改id为4的name Object[] params = {4,"www"}; JDBCUtil.update(con,sql,params,rs,st); //查询并且输出修改后的结果 String sql1= "SELECT * FROM friends"; rs=JDBCUtil.query(con,st,rs,sql1,null); while (rs.next()) { int id = rs.getInt("id"); String name = rs.getString("name"); String sex = rs.getString("sex"); System.out.println(id+" "+name+" "+sex); } }catch(SQLException e) { e.printStackTrace(); } finally{ // 关闭数据库在finally里面 JDBCUtil.release(con, st, rs); } } }
运行结果:
3.删除数据
public class Test1 { public static void main(String[] args) { Connection con = null; PreparedStatement st = null; ResultSet rs = null; try { con=JDBCUtil.getConnection(); String sql = "DELETE FROM friends WHERE id = ?"; st = con.prepareStatement(sql); //删除第6个字段 Object[] params = {6}; JDBCUtil.update(con, sql,params, rs, st); String sql1="SELECT *FROM friends"; rs=JDBCUtil.query(con, st, rs, sql1, null); while (rs.next()) { int id = rs.getInt(1); String name = rs.getString(2); String sex =rs.getString(3); System.out.println(id + " " + name +" " +sex); } } catch(SQLException e) { e.printStackTrace(); } finally { // 关闭数据库在finally里面 JDBCUtil.release(con, st, rs); } } }