1、连接数据库的五种方式
Driver driver = new Driver();
String url="jdbc:mysql://localhost:3306/a_db01";
Properties properties = new Properties();
properties.setProperty("user","root");
properties.setProperty("password","123456");
Connection connect = driver.connect(url, properties);
Statement statement = connect.createStatement();
String sql="insert into account values(null,'张三',1000)";
int i = statement.executeUpdate(sql);
statement.close();
connect.close();
Class<?> aClass = Class.forName("com.mysql.jdbc.Driver");
Driver driver = (Driver)aClass.newInstance();
String url="jdbc:mysql://localhost:3306/a_db01";
Properties properties = new Properties();
properties.setProperty("user","root");
properties.setProperty("password","123456");
Connection connect = driver.connect(url, properties);
Statement statement = connect.createStatement();
String sql="update account set balance=2000 where id=1";
int i = statement.executeUpdate(sql);
statement.close();
connect.close();
//1、5.1.6驱动加载可以不写,因为会jvm是根据jar包里面的文件名去加载
//2、驱动加载里面的静态代码块中会完成 驱动注册
Class<?> aClass = Class.forName("com.mysql.jdbc.Driver");
Driver driver = (Driver)aClass.newInstance();
DriverManager.registerDriver(driver);
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/a_db01", "root", "123456");
Statement statement = connection.createStatement();
String sql="delete from account where id=1";
statement.executeUpdate(sql);
statement.close();
connection.close();
Class<?> aClass = Class.forName("com.mysql.jdbc.Driver");
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/a_db01","root","123456");
Statement statement = connection.createStatement();
String sql="insert into account values(null,'李四',3000)";
statement.executeUpdate(sql);
statement.close();
connection.close();
Properties properties = new Properties();
properties.load(new FileInputStream("src\\my.properties"));
String driver = properties.getProperty("driver");
String url = properties.getProperty("url");
String user = properties.getProperty("user");
String password = properties.getProperty("password");
Connection connection = DriverManager.getConnection(url, user, password);
Statement statement = connection.createStatement();
String sql="insert into account values(null,'小王',1000)";
int i = statement.executeUpdate(sql);
statement.close();
connection.close();
2、查询和增删改
查询使用ResultSet resultSet = statement.executeQuery(sql);
增删改用int i = statement.executeUpdate(sql);
ResultSet底层是一个ArrayList里面存放的是数组
Properties properties = new Properties();
properties.load(new FileInputStream("src\\my.properties"));
String url = properties.getProperty("url");
String user = properties.getProperty("user");
String password = properties.getProperty("password");
String driver = properties.getProperty("driver");
Class<?> aClass = Class.forName(driver);
Connection connection = DriverManager.getConnection(url, user, password);
Statement statement = connection.createStatement();
String sql="select * from news";
ResultSet resultSet = statement.executeQuery(sql);
while (resultSet.next()){
int id = resultSet.getInt(1);
String name = resultSet.getString(2);
System.out.println(id+"\t"+name);
}
resultSet.close();
statement.close();
connection.close();
Properties properties = new Properties();
properties.load(new FileInputStream("src\\my.properties"));
String driver = properties.getProperty("driver");
String url = properties.getProperty("url");
String user = properties.getProperty("user");
String password = properties.getProperty("password");
Connection connection = DriverManager.getConnection(url, user, password);
Statement statement = connection.createStatement();
String sql="insert into account values(null,'小王',1000)";
int i = statement.executeUpdate(sql);
statement.close();
connection.close();
3、sql注入问题
preparedStatement
1、可以使用?占位符 然后通过set方法赋值
2、可以防止sql注入
3、减少编译次数提高效率
CREATE TABLE admin(
username VARCHAR(32),
PASSWORD VARCHAR(32)
)
INSERT INTO admin VALUES('admin','1234567');
SELECT * FROM admin WHERE username='root' AND PASSWORD='123456';
SELECT * FROM admin WHERE username='1'OR' and password ='OR 1='1';
statement存在sql注入
public static void main(String[] args) throws IOException, ClassNotFoundException, SQLException {
Scanner scanner = new Scanner(System.in);
System.out.print("请输入用户名:");
String username=scanner.nextLine();
System.out.print("请输入密码:");
String pass=scanner.nextLine();
Properties properties = new Properties();
properties.load(new FileInputStream("src\\my.properties"));
String url = properties.getProperty("url");
String user = properties.getProperty("user");
String password = properties.getProperty("password");
String driver = properties.getProperty("driver");
Class<?> aClass = Class.forName(driver);
Connection connection = DriverManager.getConnection(url, user, password);
Statement statement = connection.createStatement();
String sql="select * from admin where username='"+username+"' and password='"+pass+"'";
ResultSet resultSet = statement.executeQuery(sql);
if (resultSet.next()){
System.out.println("登录成功");
}else {
System.out.println("登录失败");
}
resultSet.close();
statement.close();
connection.close();
}
preparedStatement可以防止sql注入
public static void main(String[] args) throws Exception {
Scanner scanner = new Scanner(System.in);
System.out.print("请输入用户名:");
String username=scanner.nextLine();
System.out.print("请输入密码:");
String pass=scanner.nextLine();
Properties properties = new Properties();
properties.load(new FileInputStream("src\\my.properties"));
String url = properties.getProperty("url");
String user = properties.getProperty("user");
String password = properties.getProperty("password");
String driver = properties.getProperty("driver");
Class<?> aClass = Class.forName(driver);
Connection connection = DriverManager.getConnection(url, user, password);
String sql="select * from admin where username=? and password = ?";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1,username);
preparedStatement.setString(2,pass);
ResultSet resultSet = preparedStatement.executeQuery();
if (resultSet.next()){
System.out.println("登录成功");
}else {
System.out.println("登录失败");
}
resultSet.close();
preparedStatement.close();
connection.close();
}
4、JDBCUtils
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.*;
import java.util.Properties;
/**
* @author 西瓜君
* @description: TODO
* @date 2022/9/19
*/
public class JDBCUtils {
private static String driver;
private static String user;
private static String password;
private static String url;
static {
try {
Properties properties = new Properties();
properties.load(new FileInputStream("src\\my.properties"));
driver = properties.getProperty("driver");
user = properties.getProperty("user");
password = properties.getProperty("password");
url = properties.getProperty("url");
} catch (IOException e) {
throw new RuntimeException(e);
}
}
public static Connection getConnection(){
try {
return (Connection) DriverManager.getConnection(url,user,password);
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
public static void close(ResultSet resultSet, Statement statement,Connection connection){
try {
if (resultSet!=null){
resultSet.close();
}
if (statement!=null){
statement.close();
}
if (connection!=null){
connection.close();
}
}catch (Exception e){
throw new RuntimeException(e);
}
}
}
5、事物
public static void main(String[] args) {
Connection connection = JDBCUtils.getConnection();
PreparedStatement preparedStatement1 =null;
String sql1="update account set money=money-1000 where name=?";
String sql2="update account set money=money+1000 where name=?";
try {
connection.setAutoCommit(false);
preparedStatement1 = connection.prepareStatement(sql1);
preparedStatement1.setString(1,"张三");
preparedStatement1.executeUpdate();
// int i=1/0;
preparedStatement1=connection.prepareStatement(sql2);
preparedStatement1.setString(1,"李四");
preparedStatement1.executeUpdate();
connection.commit();
} catch (SQLException throwables) {
System.out.println("执行失败");
try {
connection.rollback();
} catch (SQLException e) {
e.printStackTrace();
}
throwables.printStackTrace();
}finally {
JDBCUtils.close(null,preparedStatement1,connection);
}
}
6、批处理 需要在url后面加上 rewriteBatchedStatements=true
批处理可以提高效率
@Test
public void test01() throws SQLException {
Connection connection = JDBCUtils.getConnection();
String sql="insert into admin values(null,?,?)";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
long start = System.currentTimeMillis();
for (int i = 0; i < 5000; i++) {
preparedStatement.setString(1,"jack"+i);
preparedStatement.setString(2,"666");
preparedStatement.executeUpdate();
}
long end = System.currentTimeMillis();
System.out.println(end-start);
JDBCUtils.close(null,preparedStatement,connection);
//3872
}
@Test
public void test02() throws SQLException {
Connection connection = JDBCUtils.getConnection();
String sql="insert into admin values(null,?,?)";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
long start = System.currentTimeMillis();
for (int i = 0; i < 5000; i++) {
preparedStatement.setString(1,"tom"+i);
preparedStatement.setString(2,"666");
preparedStatement.addBatch();
if ((i+1)%1000==0){
preparedStatement.executeBatch();
preparedStatement.clearBatch();
}
}
long end = System.currentTimeMillis();
System.out.println(end-start);
JDBCUtils.close(null,preparedStatement,connection);
//4025
}
7、数据库连接池
传统方式
1、不能控制连接的数量、可能会造成mysql的崩溃
2、连接过多会造成很大的网络开销
3、判断用户名和密码也会造成很大的开销
long start = System.currentTimeMillis();
for (int i = 0; i < 5000; i++) {
Connection connection = JDBCUtils.getConnection();
JDBCUtils.close(null,null,connection);
}
long end = System.currentTimeMillis();
System.out.println(end-start);
c3p0
public static void main(String[] args) throws Exception {
Properties properties = new Properties();
properties.load(new FileInputStream("src\\my.properties"));
String driver = properties.getProperty("driver");
String user = properties.getProperty("user");
String password = properties.getProperty("password");
String url = properties.getProperty("url");
ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource();
comboPooledDataSource.setDriverClass(driver);
comboPooledDataSource.setUser(user);
comboPooledDataSource.setPassword(password);
comboPooledDataSource.setJdbcUrl(url);
comboPooledDataSource.setInitialPoolSize(10);
comboPooledDataSource.setMaxPoolSize(50);
long start = System.currentTimeMillis();
for (int i = 0; i < 5000; i++) {
Connection connection = comboPooledDataSource.getConnection();
connection.close();
}
long end = System.currentTimeMillis();
System.out.println(end-start);
}
c3p0第二种使用
<c3p0-config>
<named-config name="hello">
<!-- 驱动类 -->
<property name="driverClass">com.mysql.jdbc.Driver</property>
<!-- url-->
<property name="jdbcUrl">jdbc:mysql://127.0.0.1:3306/a_db01</property>
<!-- 用户名 -->
<property name="user">root</property>
<!-- 密码 -->
<property name="password">123456</property>
<!-- 每次增长的连接数-->
<property name="acquireIncrement">5</property>
<!-- 初始的连接数 -->
<property name="initialPoolSize">10</property>
<!-- 最小连接数 -->
<property name="minPoolSize">5</property>
<!-- 最大连接数 -->
<property name="maxPoolSize">10</property>
<!-- 可连接的最多的命令对象数 -->
<property name="maxStatements">5</property>
<!-- 每个连接对象可连接的最多的命令对象数 -->
<property name="maxStatementsPerConnection">2</property>
</named-config>
</c3p0-config>
ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource("hello");
long start = System.currentTimeMillis();
for (int i = 0; i < 500000; i++) {
Connection connection = comboPooledDataSource.getConnection();
connection.close();
}
long end = System.currentTimeMillis();
System.out.println(end-start);
druid的使用
#key=value
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/a_db01?rewriteBatchedStatements=true
#url=jdbc:mysql://localhost:3306/girls
username=root
password=123456
#initial connection Size
initialSize=10
#min idle connecton size
minIdle=5
#max active connection size
maxActive=20
#max wait time (5000 mil seconds)
maxWait=5000
Properties properties = new Properties();
properties.load(new FileInputStream("src\\druid.properties"));
DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
long start = System.currentTimeMillis();
for (int i = 0; i < 500000; i++) {
Connection connection = dataSource.getConnection();
connection.close();
}
long end = System.currentTimeMillis();
System.out.println(end-start);
8、JDBCUtilsByDruid
public class JDBCUtilsByDruid {
private static DataSource dataSource;
static {
Properties properties = new Properties();
try {
properties.load(new FileInputStream("src//druid.properties"));
dataSource = DruidDataSourceFactory.createDataSource(properties);
} catch (Exception e) {
throw new RuntimeException(e);
}
}
public static Connection getConnection(){
try {
return dataSource.getConnection();
} catch (SQLException throwables) {
throw new RuntimeException(throwables);
}
}
public static void close(ResultSet resultSet, Statement statement,Connection connection){
try {
if (resultSet!=null){
resultSet.close();
}
if (statement!=null){
statement.close();
}
if (connection!=null){
connection.close();
}
}catch (Exception e){
throw new RuntimeException(e);
}
}
}
9、ResultSet
1、在关闭连接后resultSet就不能使用、不方便管理数据
2、resultSet取数据不够直观
3、 与数据库对应的class类叫做javaBean
传统写法
public static void main(String[] args) throws SQLException {
Connection connection = JDBCUtilsByDruid.getConnection();
String sql="select * from account";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
ResultSet resultSet = preparedStatement.executeQuery();
ArrayList<Account> accountArrayList = new ArrayList<>();
while (resultSet.next()){
int id = resultSet.getInt(1);
String name = resultSet.getString(2);
Double money = resultSet.getDouble(3);
Account account = new Account(id, name, money);
accountArrayList.add(account);
}
System.out.println(accountArrayList);
JDBCUtilsByDruid.close(resultSet,preparedStatement,connection);
}
改变后
@Test
public void test03() throws SQLException {
Connection connection = JDBCUtilsByDruid.getConnection();
QueryRunner queryRunner = new QueryRunner();
String sql="select * from account";
List<Account> query = queryRunner.query(connection, sql, new BeanListHandler<>(Account.class));
System.out.println(query);
JDBCUtilsByDruid.close(null,null,connection);
}