Java_JDBC及连接池

一、JDBC介绍

jdbc是java操作数据库的方式，本质是一套接口，由sun公司制定的规则，再由各个数据库厂商去实现这个接口，我们成数据库公司写的这套实现类为驱动jar包

二、好处

学习一条API就能够操作所有的关系型数据库
三、基础操作
其中executeUpdata（）用来操作DML，返回值是数据库受影响的行数；executeQuery（）用来操作DQL（select语句）返回值是ResultSet集合，里面包含查询到的数据。

使用prepareStatement：

setXxx的方法：只需要记住setObect(问号的编号, 问号的值)
编号从1开始

resultSet的操作：

resultSet.getXxx(String columnName)根据列名获取列值
Xxx表示列名的属性。

boolean next()：游标往下移动一行并返回该行是否有数据
true有数据
false没有数据

executeUpdata的使用：

//注册驱动 //如果驱动是8.0版本以上的需要加上“cj”,5.0的不用加 Class.forName("com.mysql.cj.jdbc.Driver"; //建立链接 String url = "jdbc:mysql://localhost:3306/数据库"; String username = "root"; String password = "root"; Connection connection = DriverManger.getConnection(url,username,password); //获取执行者对象 String sql= "delete from emp where eid=？"; //使用prepareStatement可以用来防止sql注入 Statement pstmt = connection.preparStatement(sql); pstmt = setObect(问号的编号, 问号的值) //executeUpdate()返回数据库受影响的行数 int row = pstmt.executeUpdate();

executeQuery的使用：

// 使用到了自己写的工具类，免去了每次建立链接的烦恼Connection conn = JdbcUtile.getj().getConn();String sql = "select * from users1 where password = ?";PreparedStatement pstmt = conn.prepareStatement(sql);pstmt.setObject(1,"123");ResultSet resultSet = pstmt.executeQuery();while(resultSet.next()){int uid = resultSet.getInt("uid");String username = resultSet.getString("username");String password = resultSet.getString("password");String nickname = resultSet.getString("nickname");System.out.println(uid+" "+username+" "+password+" "+nickname);}resultSet.close();pstmt.close();conn.close();

JdbcUtile工具类：

/* * jdbc驱动工具类： * 省去jdbc的创建连接过程 * */ public class JdbcUtile {private static Connection conn;private JdbcUtile(){}private static JdbcUtile j = new JdbcUtile();public static JdbcUtile getj(){return j;}public Connection getConn(){return conn;}// 只需执行一次所以放进静态代码块 static { // 获取类加载器ClassLoader classLoader = JdbcUtile.class.getClassLoader(); //读取properties的配置问价InputStream in = classLoader.getResourceAsStream("driver.properties");Properties properties = new Properties(); // 读入properties集合中try {properties.load(in);} catch (IOException e) {e.printStackTrace();} // 获取valueString url = properties.getProperty("url");String username = properties.getProperty("username");String password = properties.getProperty("password"); // 开启驱动，获得连接try {conn = DriverManager.getConnection(url, username, password);} catch (SQLException e) {e.printStackTrace();}} }

properties配置文件：

url=jdbc:mysql://localhost:3306/db4?serverTimezone=GMT%2B8&useSSL=false username=root password=root

三、数据库连接池

解决频繁创建链接和销毁链接的烦恼节约时间提高效率

配置连接池工具类DruidUtil：

public class DruidUtil {private static DataSource ds;static {try { // 类加载器读取配置文件Properties properties = new Properties();InputStream in = DruidUtil.class.getClassLoader().getResourceAsStream("druid.properties");properties.load(in); // 创建连接池ds = DruidDataSourceFactory.createDataSource(properties);} catch (IOException e) {e.printStackTrace();} catch (Exception e) {e.printStackTrace();}} // 获取连接public static Connection getConnection() throws SQLException {Connection connection = ds.getConnection();return connection;} // 释放资源:DMLpublic static void close(Connection conn, Statement stmt){if (stmt!=null){try {stmt.close();} catch (SQLException e) {e.printStackTrace();}}if (conn!= null){try {conn.close();} catch (SQLException e) {e.printStackTrace();}}} // 释放资源：DQLpublic static void close(Connection conn, Statement stmt, ResultSet rs){if (rs!= null){try {rs.close();} catch (SQLException e) {e.printStackTrace();}}if (stmt!=null){try {stmt.close();} catch (SQLException e) {e.printStackTrace();}}if (conn!=null){try {conn.close();} catch (SQLException e) {e.printStackTrace();}}} }

连接池properties配置文件：

url=jdbc:mysql://localhost:3306/homework?serverTimezone=GMT%2B8&useSSL=false username=root password=root driverClassName=com.mysql.cj.jdbc.Driver #初始化数据连接池 initialSize=5 #数据连接池中最多存在连接数量 maxActive=10 #最大存在时间 maxWait=3000

测试类：

public class DruidTest {@Testpublic void Druidtest() throws Exception{Connection connection = DruidUtil.getConnection();String sql = "select sid,sname,chinese from student where chinese > ?";PreparedStatement pstmt = connection.prepareStatement(sql);pstmt.setObject(1,"90");ResultSet rs = pstmt.executeQuery();while (rs.next()){int sid = rs.getInt("sid");String sname = rs.getString("sname");int chinese = rs.getInt("chinese");System.out.println("学号："+sid+"姓名："+sname+"语文成绩："+chinese);}DruidUtil.close(connection,pstmt,rs);}@Testpublic void DruidAdd() throws Exception{Connection connection = DruidUtil.getConnection();String sql = "insert into student(sid,sname,chinese) values(?,?,?)";PreparedStatement pstmt = connection.prepareStatement(sql);pstmt.setObject(1,null);pstmt.setObject(2,"chen");pstmt.setObject(3,99);int i = pstmt.executeUpdate();if (i>0){System.out.println("插入成功");}else {System.out.println("插入失败");}DruidUtil.close(connection,pstmt);}@Testpublic void DruidUpdate() throws Exception{Connection connection = DruidUtil.getConnection();String sql = "update student set sgender = ? where sname=?";PreparedStatement pstmt = connection.prepareStatement(sql);pstmt.setObject(1,"男");pstmt.setObject(2,"chen");int i = pstmt.executeUpdate();if (i>0){System.out.println("修改成功");}else {System.out.println("修改失败");}}@Testpublic void DruidDelete() throws Exception{Connection connection = DruidUtil.getConnection();String sql = "delete from student where sname = ?";PreparedStatement pstmt = connection.prepareStatement(sql);pstmt.setObject(1,"chen");int i = pstmt.executeUpdate();if (i>0){System.out.println("删除成功");}else{System.out.println("删除失败");}}

总结

以上是生活随笔为你收集整理的Java_JDBC及连接池的全部内容，希望文章能够帮你解决所遇到的问题。

如果觉得生活随笔网站内容还不错，欢迎将生活随笔推荐给好友。