Java Web之JDBC的CRUD

数据库和数据库表：

/* Navicat MySQL Data Transfer Source Server : 127.0.0.1 Source Server Version : 50718 Source Host : 127.0.0.1:3306 Source Database : test Target Server Type : MYSQL Target Server Version : 50718 File Encoding : 65001 Date: 2019-09-29 17:31:27 */ SET FOREIGN_KEY_CHECKS=0; -- ---------------------------- -- Table structure for userinfo -- ---------------------------- DROP TABLE IF EXISTS `userinfo`; CREATE TABLE `userinfo` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '用户id', `sitename` varchar(64) DEFAULT NULL COMMENT '网站名称', `username` varchar(64) NOT NULL DEFAULT '' COMMENT '账号', `password` varchar(64) NOT NULL DEFAULT '' COMMENT '账号密码', `siteadress` varchar(255) DEFAULT '' COMMENT '网站地址', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COMMENT='用户表'; -- ---------------------------- -- Records of userinfo -- ---------------------------- INSERT INTO `userinfo` VALUES ('1', 'GitHub', 'GitHub', '123456', 'https://github.com'); INSERT INTO `userinfo` VALUES ('2', 'Coding', 'Coding', '234567', 'https://coding.net'); INSERT INTO `userinfo` VALUES ('3', '', '', '345678', 'https://www.csdn.net'); INSERT INTO `userinfo` VALUES ('4', 'Oschina', 'Oschina', '456789', 'https://www.oschina.net');

1、添加数据（insert）

Connection conn = null; PreparedStatement ps = null; try { //1、注册驱动 Class.forName("com.mysql.jdbc.Driver"); //2、获取数据库连接对象Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "123456"); //3、定义Sql语句 String sql = "insert into userinfo values(?,?,?,?,?)"; //4、获取执行sql的对象prepareStatement ps = conn.prepareStatement(sql); //5、为SQL语句中的参数赋值，注意，索引是从1开始的 ps.setInt(1, 5); ps.setString(2, "baidu"); ps.setString(3, "1755128147@qq.com"); ps.setString(4, "123456"); ps.setString(5, "https://www.baidu.com/"); //6、执行操作 int num = ps.executeUpdate(); //7、处理结果 if (num > 0) { System.out.println("插入成功！！"); } } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } finally { //8、释放资源 if (ps != null) { try { ps.close(); } catch (SQLException e) { e.printStackTrace(); } } if (conn != null) { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } }

2、修改数据（update）

Connection conn = null; PreparedStatement ps = null; try { //1、注册驱动 Class.forName("com.mysql.jdbc.Driver"); //2、获取数据库连接对象Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "123456"); //3、定义Sql语句 String sql = "update userinfo set password = ? where id = ?"; //4、获取执行sql的对象prepareStatement ps = conn.prepareStatement(sql); //5、为SQL语句中的参数赋值，注意，索引是从1开始的 ps.setString(1, "654321"); ps.setInt(2, 5); //6、执行操作 int i = ps.executeUpdate(); //7、处理结果 if (i> 0) { System.out.println("修改成功！！"); } } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } finally { //8、释放资源 if (ps != null) { try { ps.close(); } catch (SQLException e) { e.printStackTrace(); } } if (conn != null) { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } }

 

3、删除数据（delete）

Connection conn = null; PreparedStatement ps = null; try { //1、注册驱动 Class.forName("com.mysql.jdbc.Driver"); //2、获取数据库连接对象Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "123456"); //3、定义Sql语句 String sql = "delete from userinfo where id = ?"; //4、获取执行sql的对象prepareStatement ps = conn.prepareStatement(sql); //5、为SQL语句中的参数赋值，注意，索引是从1开始的 ps.setInt(1, 5); //6、执行操作 int i = ps.executeUpdate(); //7、处理结果 if(num > 0) { System.out.println("删除成功"); } } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } finally { //8、释放资源 if (ps != null) { try { ps.close(); } catch (SQLException e) { e.printStackTrace(); } } if (conn != null) { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } }

4、查询数据（select）

Connection conn = null; PreparedStatement ps = null; ResultSet rs = null; try { //1、注册驱动 Class.forName("com.mysql.jdbc.Driver"); //2、获取数据库连接对象Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "123456"); //3、定义Sql语句 String sql = "select * from userinfo where id = ?"; //4、获取执行sql的对象prepareStatement ps = conn.prepareStatement(sql); //5、为SQL语句中的参数赋值，注意，索引是从1开始的 ps.setInt(1, 5); //6、执行操作 rs = ps.executeQuery(); //7、处理结果 while(rs.next()) { String sitename = rs.getString("sitename"); String siteadress = rs.getString("sitedress"); System.out.println("网站名称：" + sitename + ",网站地址：" + siteadress); } } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } finally { //8、释放资源 if(rs != null) { try { rs.close(); } catch (SQLException e) { e.printStackTrace(); } } if(ps != null){ try { ps.close(); } catch (SQLException e) { e.printStackTrace(); } } if(conn != null){ try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } }

5、数据库连接工具类

为了简化重复代码，将数据库的连接和关闭以工具类的封装。

工具类的配置文件db.properties

# db connection parameters driver=com.mysql.jdbc.Driver #driver=oracle.jdbc.driver.OracleDriver #driver=com.microsoft.sqlserver.jdbc.SQLServerDriver url=jdbc:mysql://localhost:3306/test #url=jdbc:oracle:thin:@localhost:1521:xe #url=jdbc:sqlserver://localhost:1433;DatabaseName=test username=root #username=test #username=sa password=123456

工具类

package com.wedu.demo; import java.io.FileReader; import java.io.IOException; import java.sql.*; import java.util.Properties; /** * 数据库操作工具类 */ public class JdbcUtil { private static String url; private static String username; private static String password; private static String driver; static { Properties prop = new Properties(); try { //1、加载配置文件 String path = JdbcUtil.class.getClassLoader().getResource("db.properties").getPath(); prop.load(new FileReader(path)); //2、获取加载的数据 url = prop.getProperty("url"); username = prop.getProperty("username"); password = prop.getProperty("password"); driver = prop.getProperty("driver"); //3、注册驱动 Class.forName(driver); } catch (IOException e) { e.printStackTrace(); } catch (ClassNotFoundException e) { e.printStackTrace(); } } /** * 获取数据库连接对象Connection * @return 连接对象Connection * @throws SQLException */ public static Connection getConnection() throws SQLException { return DriverManager.getConnection(url,username,password); } /** * 释放资源 * @param st 执行sql语句对象 * @param conn 数据库连接对象 */ public static void close(Statement st,Connection conn) { if(st != null){ try { st.close(); } catch (SQLException e) { e.printStackTrace(); } } if(conn != null){ try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } /** * 释放资源 * @param rs 结果集对象 * @param st 执行sql语句对象 * @param conn 数据库连接对象 */ public static void close(ResultSet rs, Statement st,Connection conn) { if(rs != null){ try { rs.close(); } catch (SQLException e) { e.printStackTrace(); } } if(st != null){ try { st.close(); } catch (SQLException e) { e.printStackTrace(); } } if(conn != null){ try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } }

工具类的测试

package com.wedu.demo; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; public class Demo { public static void main(String[] args) { Connection conn = null; PreparedStatement ps = null; ResultSet rs = null; try { //1、获取数据库连接 conn = JdbcUtil.getConnection(); //2、定义Sql语句 String sql = "select * from userinfo where id = ?"; //3、获取执行sql的对象prepareStatement ps = conn.prepareStatement(sql); //4、为SQL语句中的参数赋值，注意，索引是从1开始的 ps.setInt(1, 4); //5、执行操作 rs = ps.executeQuery(); //6、处理结果 while(rs.next()) { String sitename = rs.getString("sitename"); String siteadress = rs.getString("siteadress"); System.out.println("网站名称：" + sitename + ",网站地址：" + siteadress); } } catch (SQLException e) { e.printStackTrace(); } finally { //7、释放资源 JdbcUtil.close(rs,ps,conn); } } }