Java数据库连接，（Java Database Connectivity，简称JDBC）是Java语言中用来规范客户端程序如何来访问数据库的应用程序接口，提供了诸如查询和更新数据库中数据的方法。JDBC也是Sun Microsystems的商标。我们通常说的JDBC是面向关系型数据库的。
创建一个工具类
package com.www.qi.utils; import java.io.IOException; import java.io.InputStream; import java.sql.*; import java.util.Properties; public class JdbcUtils { private static String driver = null; private static String url = null; static { try { InputStream is = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties"); Properties properties = new Properties(); properties.load(is); driver = properties.getProperty("driver"); url = properties.getProperty("url"); Class.forName(driver); } catch (IOException | ClassNotFoundException e) { e.printStackTrace(); } } //获取连接 public static Connection getConnection() throws SQLException { return DriverManager.getConnection(url); } //释放连接 public static void release(Connection connection, Statement statement, ResultSet resultSet) throws SQLException { if (resultSet != null){ resultSet.close(); } if(statement != null){ statement.close(); } if (connection != null){ connection.close(); } } }实现数据库的插入操作
package com.www.qi; import com.www.qi.utils.JdbcUtils; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class TestInsert { public static void main(String[] args) throws SQLException { Connection connection = null; Statement statement = null; ResultSet resultSet = null; try { //获取数据库连接 connection = JdbcUtils.getConnection(); //获得SQL的执行对象 statement = connection.createStatement(); String sql = "INSERT INTO users VALUES(5,'刘奇','123654','787856@qq.com','1997-11-18')"; int i = statement.executeUpdate(sql); if (i > 0){ System.out.println("插入成功！"); } } catch (SQLException e) { e.printStackTrace(); }finally { JdbcUtils.release(connection,statement,resultSet); } } }实现数据库的修改操作
package com.www.qi; import com.www.qi.utils.JdbcUtils; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class TestUpdate { public static void main(String[] args) throws SQLException { Connection connection = null; Statement statement =null; ResultSet resultSet = null; try { connection = JdbcUtils.getConnection(); statement = connection.createStatement(); String sql = "UPDATE users SET `name`='嘵奇',email='458254@qq.com' WHERE id=1"; int i = statement.executeUpdate(sql); if (i > 0){ System.out.println("修改成功！"); } } catch (SQLException e) { e.printStackTrace(); }finally { JdbcUtils.release(connection,statement,resultSet); } } }实现数据库的删除操作
package com.www.qi; import com.www.qi.utils.JdbcUtils; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class TestDelete { public static void main(String[] args) throws SQLException { Connection connection = null; Statement statement = null; ResultSet resultSet = null; try { connection = JdbcUtils.getConnection(); statement = connection.createStatement(); String sql = "DELETE FROM users WHERE id = 5"; int i = statement.executeUpdate(sql); if (i > 0){ System.out.println("删除成功！"); } } catch (SQLException e) { e.printStackTrace(); }finally{ JdbcUtils.release(connection,statement,resultSet); } } }实现数据库的查询操作
package com.www.qi; import com.www.qi.utils.JdbcUtils; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class TestSelect { public static void main(String[] args) throws SQLException { Connection connection = null; Statement statement = null; ResultSet resultSet = null; try { connection = JdbcUtils.getConnection(); statement = connection.createStatement(); String sql = "SELECT `name`,email,pwd FROM users WHERE id = 1 "; resultSet = statement.executeQuery(sql); while (resultSet.next()){ System.out.println("姓名：" + resultSet.getString("name")); System.out.println("邮箱：" + resultSet.getString("email")); System.out.println("密码：" + resultSet.getString("pwd")); } } catch (SQLException e) { e.printStackTrace(); }finally { JdbcUtils.release(connection,statement,resultSet); } } }SQL注入即是指web应用程序对用户输入数据的合法性没有判断或过滤不严，攻击者可以在web应用程序中事先定义好的查询语句的结尾上添加额外的SQL语句，在管理员不知情的情况下实现非法操作，以此来实现欺骗数据库服务器执行非授权的任意查询，从而进一步得到相应的数据信息。
百度百科
可以防止SQL注入，效率会更高 PreparedStatement会把传递进来的参数当作字符 假设其中存在转义字符会被直接转义
利用PreparedStatement实现查询操作
package com.www.qi; import com.www.qi.utils.JdbcUtils; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; public class TestPre03 { public static void main(String[] args) throws SQLException { Connection connection = null; PreparedStatement ps = null; ResultSet resultSet = null; try { connection = JdbcUtils.getConnection(); String sql = "select * from users where id = ?"; ps = connection.prepareStatement(sql); ps.setInt(1,1); //查询结束返回一个结果值 resultSet = ps.executeQuery(); if (resultSet.next()){ System.out.println("序号：" + resultSet.getInt("id")); System.out.println("姓名：" + resultSet.getString("name")); System.out.println("密码：" + resultSet.getString("pwd")); System.out.println("邮箱：" + resultSet.getString("email")); System.out.println("生日：" + resultSet.getDate("birthday")); } } catch (SQLException e) { e.printStackTrace(); }finally { JdbcUtils.release(connection,ps,resultSet); } } }利用PreparedStatement实现插入操作
package com.www.qi; import com.www.qi.utils.JdbcUtils; import java.sql.*; public class TestPre01 { public static void main(String[] args) throws SQLException { Connection connection = null; PreparedStatement ps = null; try { connection = JdbcUtils.getConnection(); //使用 ？ 占位符代替参数 String sql = "insert into users(id,`name`,`pwd`,`email`,`birthday`) values(?,?,?,?,?)"; //预编译SQL，编写SQL但不执行 ps = connection.prepareStatement(sql); //为参数赋值 ps.setInt(1,7); ps.setString(2,"嘵奇"); ps.setString(3,"852147"); ps.setString(4,"89561@qq.com"); ps.setDate(5,new java.sql.Date(new java.util.Date().getTime())); int i = ps.executeUpdate(); if (i > 0){ System.out.println("插入成功！"); } } catch (SQLException e) { e.printStackTrace(); }finally { JdbcUtils.release(connection,ps,null); } } }利用PreparedStatement实现更新操作
package com.www.qi; import com.www.qi.utils.JdbcUtils; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.SQLException; public class TestPre02 { public static void main(String[] args) { Connection connection = null; PreparedStatement ps = null; try { connection = JdbcUtils.getConnection(); String sql = "update users set `name` = ?,`pwd` = ? where id = ?"; ps = connection.prepareStatement(sql); ps.setString(1,"老詹"); ps.setString(2,"741258"); ps.setInt(3,6); int i = ps.executeUpdate(); if (i > 0){ System.out.println("更新成功！"); } } catch (SQLException e) { e.printStackTrace(); } } }利用PreparedStatement实现删除操作
package com.www.qi; import com.www.qi.utils.JdbcUtils; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.SQLException; public class TestPre04 { public static void main(String[] args) throws SQLException { Connection connection = null; PreparedStatement ps = null; try { connection = JdbcUtils.getConnection(); String sql = "delete from users where id = ?"; ps = connection.prepareStatement(sql); ps.setInt(1,2); int i = ps.executeUpdate(); if (i > 0){ System.out.println("删除成功！"); } } catch (SQLException e) { e.printStackTrace(); }finally { JdbcUtils.release(connection,ps,null); } } }配置文件
#连接设置 driverClassName=com.mysql.cj.jdbc.Driver url= jdbc:mysql://localhost:3306/jdbcstudy?characterEncoding=utf8&useSSL=false&serverTimezone=UTC&rewriteBatchedStatements=true username=root password=123456 #<!-- 初始化连接 --> initialSize=10 #最大连接数量 maxActive=50 #<!-- 最大空闲连接 --> maxIdle=20 #<!-- 最小空闲连接 --> minIdle=5 #<!-- 超时等待时间以毫秒为单位 6000毫秒/1000等于60秒 --> maxWait=60000 #JDBC驱动建立连接时附带的连接属性属性的格式必须为这样：[属性名=property;] #注意："user" 与 "password" 两个属性会被明确地传递，因此这里不需要包含他们。 connectionProperties=useUnicode=true;characterEncoding=UTF8 #指定由连接池所创建的连接的自动提交（auto-commit）状态。 defaultAutoCommit=true #driver default 指定由连接池所创建的连接的事务级别（TransactionIsolation）。 #可用值为下列之一：（详情可见javadoc。）NONE,READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ, SERIALIZABLE defaultTransactionIsolation=READ_UNCOMMITTED创建工具类
package com.www.qi.utils; import org.apache.commons.dbcp.BasicDataSourceFactory; import javax.sql.DataSource; import java.io.InputStream; import java.sql.*; import java.util.Properties; public class DbcpUtils { private static DataSource dataSource = null; static { try { InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("dbcpconfig.properties"); Properties properties = new Properties(); properties.load(in); //创建数据源 dataSource = BasicDataSourceFactory.createDataSource(properties); } catch (Exception e) { e.printStackTrace(); } } //获取连接 public static Connection getConnection() throws SQLException { //从数据源中获取连接 return dataSource.getConnection(); } //释放连接资源 public static void release(Connection connection, PreparedStatement ps, ResultSet resultSet) throws SQLException { if (resultSet != null){ try { resultSet.close(); } catch (SQLException e) { e.printStackTrace(); } } if (ps != null){ try { ps.close(); } catch (SQLException e) { e.printStackTrace(); } } if (connection != null){ connection.close(); } } }测试类：
package com.www.qq; import com.www.qi.utils.DbcpUtils; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.SQLException; public class TestDbcp01 { public static void main(String[] args) throws SQLException { Connection connection = null; PreparedStatement ps = null; try { connection = DbcpUtils.getConnection(); String sql = "insert into users(id,`name`,`pwd`,`email`,`birthday`) values(?,?,?,?,?)"; ps = connection.prepareStatement(sql); ps.setInt(1,11); ps.setString(2,"浓眉"); ps.setString(3,"000000"); ps.setString(4,"11111@qq.com"); ps.setString(5,"1990-10-10"); int i = ps.executeUpdate(); if (i > 0){ System.out.println("插入成功！"); }else { System.out.println("插入失败！"); } } catch (SQLException e) { e.printStackTrace(); }finally { DbcpUtils.release(connection,ps,null); } } }配置文件
<?xml version="1.0" encoding="UTF-8" ?> <c3p0-config> //默认的配置 <default-config> <property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbcstudy?&useSSL=false&serverTimezone=UTC</property> <property name="driverClass">com.mysql.cj.jdbc.Driver</property> <property name="user">root</property> <property name="password">123456</property> <property name="acquireIncrement">3</property> <property name="initialPoolSize">10</property> <property name="minPoolSize">2</property> <property name="maxPoolSize">10</property> </default-config> </c3p0-config>创建工具类
package com.www.qi.utils; import com.mchange.v2.c3p0.ComboPooledDataSource; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import javax.sql.DataSource; public class C3P0Utils { private static ComboPooledDataSource dataSource = new ComboPooledDataSource(); public static DataSource getDataSource(){ return dataSource; } public static Connection getConnection(){ try { return dataSource.getConnection(); } catch (SQLException e) { throw new RuntimeException(); } } public static void release(Connection connection, PreparedStatement ps, ResultSet resultSet) throws SQLException { if (resultSet != null){ try { resultSet.close(); } catch (SQLException e) { e.printStackTrace(); } } if (ps != null){ try { ps.close(); } catch (SQLException e) { e.printStackTrace(); } } if (connection != null){ connection.close(); } } }测试类：
package com.www.qq; import com.www.qi.utils.C3P0Utils; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.SQLException; public class TestC3P01 { public static void main(String[] args) throws SQLException { Connection connection = null; PreparedStatement ps = null; try { connection = C3P0Utils.getConnection(); String sql = "insert into users(id,`name`,`pwd`,`email`,`birthday`) values(?,?,?,?,?)"; ps = connection.prepareStatement(sql); ps.setInt(1,10); ps.setString(2,"詹姆斯"); ps.setString(3,"202020"); ps.setString(4,"202020@qq.com"); ps.setString(5,"1988-12-12"); int i = ps.executeUpdate(); if (i > 0){ System.out.println("插入成功！"); }else { System.out.println("插入失败！"); } } catch (SQLException e) { e.printStackTrace(); }finally { C3P0Utils.release(connection,ps,null); } } }