jdbc
数据库驱动
驱动:声卡,显卡,网卡
导入mysql的数据库驱动
jdbc是什么
sum公司为了简化开发人员的操作,提供了一个规范 俗称jdbc
这些规范的接口由具体的厂商去实现
加载驱动
import java.sql.*;
public class StartUp {
private final static String url = "jdbc:mysql://127.0.0.1:3306/jdbcStudy?useUnicode=true&characterEncoding=utf8&useSSL=true";
private final static String name = "root";
private final static String pwd = "xxx";
public static void main(String[] args) throws ClassNotFoundException {
//加载驱动
Class.forName("com.mysql.cj.jdbc.Driver");
Connection connection = null;
Statement statement=null;
ResultSet res=null;
try {
//获取连接对象
connection = DriverManager.getConnection(url, name, pwd);
//获取数据库执行对象
statement = connection.createStatement();
String sql="select * from users";
//执行sql
res = statement.executeQuery(sql);
while (res.next())
{
System.out.println(""+res.getInt("id")+res.getString("name")+res.getString("password")+res.getString("email")+
res.getDate("birthday"));
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
//关闭连接
if (res!=null) {
try {
res.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(statement!=null)
{
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
DriverManager对象是单例模式
sql执行的几种选择
statement.execute();//执行所有类型的sql
statement.executeQuery();//执行查询sql
statement.executeUpdate();//执行删除更新修改sql
如何遍历结果集
res.beforeFirst();//移动到最前面
res.afterLast();//移动到最后面
res.previous();//移动到前一行
res.absolute();//移动到指定行
res.next();//移动到下一行,最初调用时不动
通过反射加载资源
static {
InputStream db = null;
try {
db = StartUp.class.getClassLoader().getResourceAsStream("db.properties");
Properties props = new Properties();
props.load(db);
driver = props.getProperty("driver");
url = props.getProperty("url");
name = props.getProperty("name");
pwd = props.getProperty("pwd");
} catch (Exception e) {
System.out.println(e.getMessage());
} finally {
if (db != null) {
try {
db.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
SQL注入问题
sql存在漏洞,字符串会被拼接导致数据泄露
select *from jdbcStudy.users where name='王五' AND password=' 1'or'1=1'
字符串会拼接
PreparedStatement对象可防止sql注入.效率更高
import com.pjy.Main.StartUp;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class PreprocessingSql {
private static ResultSet res;
public static void main(String[] args) {
PreparedStatement pst = null;
try {
String sql = "select * from jdbcStudy.users where name=? and password=?";
pst = StartUp.getConnection().prepareStatement(sql);
//默认是从第一个开始的
pst.setString(1, "张三");
pst.setString(2, "12323");
res = pst.executeQuery();
while (res.next()) {
System.out.println("" + res.getInt("id") + res.getString("name") + res.getString("password") + res.getString("email") +
res.getDate("birthday"));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
StartUp.close();
try {
res.close();
pst.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
事务管理
事务处理核心代码
getConnection().setAutoCommit(false);//开启事务
//设置保存点
Savepoint savepoint = getConnection().setSavepoint("upd1");
//回滚事务
getConnection().rollback(savepoint);
//提交事务
getConnection().commit();
import com.pjy.Main.StartUp;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Savepoint;
import java.util.Properties;
public class Affairs {
private static Connection connection;
private static PreparedStatement preparedStatement;
private static String driver;
private static String url;
private static String name;
private static String pwd;
public Affairs() {
}
public static synchronized Connection getConnection() throws SQLException {
if (connection == null) {
connection = DriverManager.getConnection(url, name, pwd);
}
return connection;
}
public static synchronized int executeUpdate(String sql, Object[] data) throws SQLException {
getConnection();
preparedStatement = connection.prepareStatement(sql);
int result;
for(result = 0; result < data.length; ++result) {
preparedStatement.setObject(result + 1, data[result]);
}
result = preparedStatement.executeUpdate();
close((Connection)null, preparedStatement);
return result;
}
public static synchronized ResultSet executeQuery(String sql, Object[] data) throws SQLException {
getConnection();
preparedStatement = connection.prepareStatement(sql);
if (data != null) {
for(int i = 0; i < data.length; ++i) {
preparedStatement.setObject(i + 1, data[i]);
}
}
return preparedStatement.executeQuery();
}
public static void close(Connection conn, PreparedStatement ptx) {
try {
if (conn != null) {
conn.close();
}
if (ptx != null) {
ptx.close();
}
} catch (SQLException var3) {
var3.printStackTrace();
}
}
public static void main(String[] args) throws SQLException {
String sql = "select *from jdbcStudy.users where name=?";
Object[] data = new Object[]{"张三"};
ResultSet set = executeQuery(sql, data);
while(set.next()) {
System.out.println("" + set.getInt("id") + set.getString("name") + set.getString("password") + set.getString("email") + set.getDate("birthday"));
}
System.out.println("-------------------------");
getConnection().setAutoCommit(false);
set.close();
Savepoint savepoint = getConnection().setSavepoint("upd1");
sql = "update users set name =? where id =?";
executeUpdate(sql, new Object[]{"丽水", 1});
sql = "select *from jdbcStudy.users";
set = executeQuery(sql, (Object[])null);
while(set.next()) {
System.out.println("" + set.getInt("id") + set.getString("name") + set.getString("password") + set.getString("email") + set.getDate("birthday"));
}
set.close();
getConnection().rollback(savepoint);
getConnection().commit();
close(getConnection(), (PreparedStatement)null);
}
static {
InputStream db = null;
try {
db = StartUp.class.getClassLoader().getResourceAsStream("db.properties");
Properties props = new Properties();
props.load(db);
driver = props.getProperty("driver");
url = props.getProperty("url");
name = props.getProperty("name");
pwd = props.getProperty("pwd");
Class.forName(driver);
} catch (Exception var10) {
System.out.println(var10.getMessage());
} finally {
if (db != null) {
try {
db.close();
} catch (IOException var9) {
var9.printStackTrace();
}
}
}
}
}
数据库连接池
池化技术
每次连接释放都很浪费资源
准备一下预先的资源,使用一下资源不立即释放而是归还给连接池进行下次分配的管理
常用设置
常用连接数:
最小连接数:
最大连接数:
等待超时:
编写连接池只需要实现一个DataSource接口
一般不用自己写有好多连接池已经写好了直接拿来用就行了
开源的数据库连接池
-
c3p0
需要的jar包Mchange Commons Java » 0.2.20和C3P0 » 0.9.5.5
配置文件
<?xml version="1.0" encoding="UTF-8"?> <c3p0-config> <!-- c3p0的缺省(默认)配置 如果在代码中"ComboPooledDataSource ds=new ComboPooledDataSource();"这样写就表示使用的是c3p0的缺省(默认)--> <named-config name="MySQL"> <property name="driverClass">com.mysql.cj.jdbc.Driver</property> <property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbcStudy?useUnicode=true&characterEncoding=utf8&useSSL=true</property> <property name="user">xxxx</property> <property name="password">xxxx</property> <property name="acquiredIncrement">5</property> <property name="initialPoolSize">10</property> <property name="minPoolSize">5</property> <property name="maxPoolSize">20</property> </named-config> </c3p0-config>
java代码
import com.mchange.v2.c3p0.ComboPooledDataSource; import java.sql.*; public class C3p0US { private static ComboPooledDataSource dataSource; static { dataSource=new ComboPooledDataSource("MySQL"); } public static Connection getConnection() throws SQLException { //从数据源中获取连接 return dataSource.getConnection(); } public synchronized static Object execute(Connection cn, PreparedStatement preparedStatement, String sql, Object[] data, boolean model) throws SQLException { preparedStatement = cn.prepareStatement(sql); if (data != null) { for (int i = 0; i < data.length; i++) { preparedStatement.setObject(i + 1, data[i]); } } Object result = model ? preparedStatement.executeQuery() : preparedStatement.executeUpdate(); return result; } /* * 注意:当Statement对象关闭时,其当前的ResultSet对象(如果存在)也将关闭。 * */ public static void close(Connection conn, PreparedStatement ptx) { try { if (conn != null) conn.close(); if (ptx != null) ptx.close(); } catch (SQLException e) { e.printStackTrace(); } } public static void main(String[] args) throws SQLException { String sql = "select *from jdbcStudy.users where name=?"; Object[] data = {"张三"}; Connection conn = getConnection(); PreparedStatement ptx = null; ResultSet set = (ResultSet) execute(conn, ptx, sql, data, true); while (set.next()) { System.out.println("" + set.getInt("id") + set.getString("name") + set.getString("password") + set.getString("email") + set.getDate("birthday")); } System.out.println("-------------------------"); close(null, ptx); conn.setAutoCommit(false);//开启事务 //设置保存点 Savepoint savepoint = conn.setSavepoint("upd1"); sql = "update users set name =? where id =?"; execute(conn, ptx, sql, new Object[]{"丽水", 1}, false); sql = "select *from jdbcStudy.users"; set = (ResultSet) execute(conn, ptx, sql, null, true); while (set.next()) { System.out.println("" + set.getInt("id") + set.getString("name") + set.getString("password") + set.getString("email") + set.getDate("birthday")); } set.close(); //回滚事务 conn.rollback(savepoint); //提交事务 conn.commit(); close(null, ptx); close(conn, null); } }
-
dbcp
需要的jar包Commons Pool与Commons DBCP
配置文件
driverClassName=com.mysql.cj.jdbc.Driver url=jdbc:mysql://localhost:3306/jdbcStudy?useUnicode=true&characterEncoding=utf8&useSSL=true username=xxxxx password=xxxxx #初始化连接 initialSize=10 #最大连接数量 maxActive=50 #最大空闲连接数量 maxIdle=20 #最小空闲连接数 minIdle=5 #等待超时 毫秒为单位 maxWait=60000 #JDBC驱动建立连接时附带的连接属性属性的格式必须为这样:【属性名=property;】 #注意:user 与 password 两个属性会被明确地传递,因此这里不需要包含他们。 connectionProperties=useUnicode=true;characterEncoding=UTF8 #指定由连接池所创建的连接的自动提交(auto-commit)状态。 defaultAutoCommit=true #driver default 指定由连接池所创建的连接的只读(read-only)状态。 #如果没有设置该值,则“setReadOnly”方法将不被调用。(某些驱动并不支持只读模式,如:Informix) defaultReadOnly= #driver default 指定由连接池所创建的连接的事务级别(TransactionIsolation)。 #可用值为下列之一:(详情可见javadoc。)NONE,READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ, SERIALIZABLE defaultTransactionIsolation=READ_UNCOMMITTED
java代码
import com.pjy.Main.StartUp; import org.apache.commons.dbcp.BasicDataSourceFactory; import javax.sql.DataSource; import java.io.IOException; import java.io.InputStream; import java.sql.*; import java.util.Properties; public class DbcUS { private static DataSource dataSource; //初始化 static { InputStream db = null; try { db = StartUp.class.getClassLoader().getResourceAsStream("dbcpconfig.properties"); Properties props = new Properties(); props.load(db); //创建数据源,工厂设计模式 dataSource = BasicDataSourceFactory.createDataSource(props); } catch (Exception e) { System.out.println(e.getMessage()); } finally { if (db != null) { try { db.close(); } catch (IOException e) { e.printStackTrace(); } } } } public static Connection getConnection() throws SQLException { //从数据源中获取连接 return dataSource.getConnection(); } public synchronized static Object execute(Connection cn, PreparedStatement preparedStatement, String sql, Object[] data, boolean model) throws SQLException { preparedStatement = cn.prepareStatement(sql); if (data != null) { for (int i = 0; i < data.length; i++) { preparedStatement.setObject(i + 1, data[i]); } } Object result = model ? preparedStatement.executeQuery() : preparedStatement.executeUpdate(); return result; } /* * 注意:当Statement对象关闭时,其当前的ResultSet对象(如果存在)也将关闭。 * */ public static void close(Connection conn, PreparedStatement ptx) { try { if (conn != null) conn.close(); if (ptx != null) ptx.close(); } catch (SQLException e) { e.printStackTrace(); } } public static void main(String[] args) throws SQLException { String sql = "select *from jdbcStudy.users where name=?"; Object[] data = {"张三"}; Connection conn = getConnection(); PreparedStatement ptx = null; ResultSet set = (ResultSet) execute(conn, ptx, sql, data, true); while (set.next()) { System.out.println("" + set.getInt("id") + set.getString("name") + set.getString("password") + set.getString("email") + set.getDate("birthday")); } System.out.println("-------------------------"); close(null, ptx); conn.setAutoCommit(false);//开启事务 //设置保存点 Savepoint savepoint = conn.setSavepoint("upd1"); sql = "update users set name =? where id =?"; execute(conn, ptx, sql, new Object[]{"丽水", 1}, false); sql = "select *from jdbcStudy.users"; set = (ResultSet) execute(conn, ptx, sql, null, true); while (set.next()) { System.out.println("" + set.getInt("id") + set.getString("name") + set.getString("password") + set.getString("email") + set.getDate("birthday")); } set.close(); //回滚事务 conn.rollback(savepoint); //提交事务 conn.commit(); close(null, ptx); close(conn, null); } }
-
HikariCP
-
Druid
结论无论使用什么数据源,DataSource的接口方法都不会变