如何通过JDBC实现数据库的连接、查询、更新和删除等操作？

本文共计945个文字，预计阅读时间需要4分钟。

使用JDBC操作数据库，实现基本的增删改查操作。开发环境为JDK1.8.0_151和Eclipse-jee-neon-3，项目包含DBHelper.java文件，位于mysql.jdbc包中。代码示例：

javaimport java.sql.Connection;import java.sql.DriverManager;

public class DBHelper { // 数据库连接 public static Connection getConnection() throws Exception { Class.forName(com.mysql.jdbc.Driver); String url=jdbc:mysql://localhost:3306/数据库名称; String user=用户名; String password=密码; return DriverManager.getConnection(url, user, password); }

// 增加数据 public static void insert(String sql) throws Exception { Connection conn=getConnection(); try { conn.setAutoCommit(false); java.sql.Statement stmt=conn.createStatement(); stmt.executeUpdate(sql); conn.commit(); } finally { conn.close(); } }

// 删除数据 public static void delete(String sql) throws Exception { Connection conn=getConnection(); try { conn.setAutoCommit(false); java.sql.Statement stmt=conn.createStatement(); stmt.executeUpdate(sql); conn.commit(); } finally { conn.close(); } }

// 修改数据 public static void update(String sql) throws Exception { Connection conn=getConnection(); try { conn.setAutoCommit(false); java.sql.Statement stmt=conn.createStatement(); stmt.executeUpdate(sql); conn.commit(); } finally { conn.close(); } }

// 查询数据 public static java.sql.ResultSet query(String sql) throws Exception { Connection conn=getConnection(); try { java.sql.Statement stmt=conn.createStatement(); return stmt.executeQuery(sql); } finally { conn.close(); } }}

使用JDBC操作数据库

简单的对数据库进行增删改查操作 开发环境 JDK1.8.0_151 Eclipse-jee-neon-3-win32-x86_64 DBHelper.java

package mysql.jdbc; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; /*** * @ProjectName: JDBC * @PackageName: mysql.jdbc * @ClassName: DBHelper * @Description: 封装DBHelper类，对数据库实现 增删改查 * @Author： 伏永正 * @Date： 2017年10月31日 下午1:43:14 */ public class DBHelper { private static String url="jdbc:mysql://localhost:3306/smxpt"; //数据库地址jdbc:mysql://连接主机ip:端口号/数据库名 private static String username="root"; //数据库用户名 private static String password="root"; //数据库用户密码 /*** * @MethodName: SQLUpdate * @Description: 封装SQLUpdate方法提供SQL语句及params[]参数，对数据库实现增删改功能 * @Params: @param sql * @Params: @param params * @Params: @return * @Return: boolean * @Author: 伏永正 * @Date: 2017年10月31日下午1:21:09 * @throws */ public static boolean SQLUpdate(String sql,Object[] params) { try { //加载MySQL驱动 Class.forName("com.mysql.jdbc.Driver"); //获得数据库连接对象 Connection connection = DriverManager.getConnection(url, username, password); //调用连接对象connection的prepareStatement方法获取SQL语句预处理对象 PreparedStatement prepareStatement = connection.prepareStatement(sql); //判断params数组参数的个数，并调用prepareStatement的setXXX方法赋值给？占位符 for (int i = 1; i <= params.length; i++) { prepareStatement.setObject(i, params[i-1]); } //用preparedStatement的executeUpdate方法执行SQL语句 int row = prepareStatement.executeUpdate(); //判断是否成功 if (row == 0) { return false; }else { return true; } } catch (ClassNotFoundException e) { e.printStackTrace(); throw new RuntimeException("警告：加载MySQL驱动失败！！"); } catch (SQLException e) { e.printStackTrace(); throw new RuntimeException("警告：获取链接对象失败！！"); } } /*** * @MethodName: Select * @Description: 封装Select方法提供SQL语句及params[]参数，对数据库实现查询功能 无参数传null * @Params: @param sql * @Params: @param params * @Params: @return * @Return: ResultSet * @Author: 伏永正 * @Date: 2017年10月31日下午3:11:17 * @throws */ public static ResultSet Select(String sql,Object[] params) { try { //加载MySQL驱动 Class.forName("com.mysql.jdbc.Driver"); //获得数据库连接对象 Connection connection = DriverManager.getConnection(url, username, password); //调用连接对象connection的得preparedStatrment获取SQL语句预编译对象 PreparedStatement preparedStatement = connection.prepareStatement(sql); //判断params数组参数的个数，并调用prepareStatement的setXXX方法赋值给？占位符 if (params != null) { for (int i = 1; i <= params.length; i++) { preparedStatement.setObject(i, params[i-1]); } } //用preparedStatement的executeQuery方法执行SQL语句 ResultSet resultSet = preparedStatement.executeQuery(); return resultSet; } catch (ClassNotFoundException e) { e.printStackTrace(); throw new RuntimeException("警告：加载MySQL驱动失败！！"); } catch (SQLException e) { e.printStackTrace(); throw new RuntimeException("警告：获取链接对象失败！！"); } } } TestDBHelper.java

package mysql.jdbc; import java.sql.ResultSet; import java.sql.SQLException; public class TestDBHelper { public static void main(String[] args) { /*//增 String sql ="INSERT INTO admins (uname,pwd,phone) VALUES (?,?,?);"; Object[] params ={"roor","root",789678}; if (DBHelper.SQLUpdate(sql, params)) { System.out.println("添加成功"); } else { System.out.println("添加失败"); } //删 String sql1 ="DELETE FROM admins WHERE uname=? ;"; Object[] params1 ={"admin"}; if (DBHelper.SQLUpdate(sql1, params1)) { System.out.println("删除成功"); } else { System.out.println("删除失败"); } //改 String sql2 ="UPDATE admins SET uname=?,pwd=? WHERE uname=? ;"; Object[] params2 ={"admin","admin","root"}; if (DBHelper.SQLUpdate(sql2, params2)) { System.out.println("删除成功"); } else { System.out.println("删除失败"); } //查 String sql3 ="SELECT COUNT(*) FROM admins WHERE uname=?;"; Object[] params2 ={"admin"}; ResultSet select = DBHelper.Select(sql3, params2); try { if (select.next()) { int int1 = select.getInt(1); System.out.println(int1); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } */ String sql3 ="SELECT * FROM admins;;"; // Object[] params2 ={"admin"}; ResultSet select = DBHelper.Select(sql3, null); try { if (select.next()) { System.out.println(select.getInt("id")+"\t"+select.getString("uname")); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } } mysql-connector-java-5.1.39-bin.jar mysql-connector-java-5.1.39-bin.jar