Skip to content

Commit b55fd7c

Browse files
author
nononi
committed
第五周作业10
1 parent 30d1e92 commit b55fd7c

6 files changed

Lines changed: 433 additions & 0 deletions

File tree

‎04fx/homework10/src/main/java/io/nononi/db/dbdemo/jdbc/HIkariDemo.java‎

Lines changed: 76 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,76 @@
1+
package io.nononi.db.dbdemo.jdbc;
2+
3+
import com.zaxxer.hikari.HikariConfig;
4+
import com.zaxxer.hikari.HikariDataSource;
5+
6+
import java.sql.Connection;
7+
import java.sql.PreparedStatement;
8+
import java.sql.SQLException;
9+
import java.util.Random;
10+
11+
/**
12+
* 使用Hikati连接池
13+
*/
14+
public class HIkariDemo {
15+
private static HikariDataSource dataSource;
16+
17+
static {
18+
19+
//初始化HikariConfig配置
20+
HikariConfig config = new HikariConfig();
21+
config.setJdbcUrl("jdbc:mysql://localhost:3306/test");
22+
config.setUsername("root");
23+
config.setPassword("root");
24+
config.setDriverClassName("com.mysql.jdbc.Driver");
25+
config.setMaximumPoolSize(20);
26+
config.setMinimumIdle(10);
27+
config.setAutoCommit(false);
28+
config.setConnectionTimeout(30000);
29+
//初始化HikariDataSource
30+
dataSource = new HikariDataSource(config);
31+
}
32+
33+
public static Connection getConnection() throws SQLException {
34+
return dataSource.getConnection();
35+
}
36+
37+
public static void main(String[] args) throws SQLException {
38+
Random random = new Random();
39+
Connection conn = null;
40+
try {
41+
conn = getConnection();
42+
conn.setAutoCommit(false);
43+
String sql = "INSERT INTO `user` (id, name, pwd) VALUES(?, ?, ?);";
44+
PreparedStatement preparedStatement = conn.prepareStatement(sql);
45+
long begin = System.currentTimeMillis();
46+
for (int i = 0; i < 100000; i++) {
47+
preparedStatement.setInt(1, i + 1);
48+
preparedStatement.setString(2, "name_" + i);
49+
preparedStatement.setInt(3, random.nextInt(9999));
50+
preparedStatement.addBatch();
51+
if ((i + 1) % 200 == 0) {
52+
preparedStatement.executeBatch();
53+
preparedStatement.clearBatch();
54+
}
55+
}
56+
if (100000 % 200 != 0) {
57+
preparedStatement.executeBatch();
58+
preparedStatement.clearBatch();
59+
}
60+
conn.commit();
61+
long end = System.currentTimeMillis();
62+
System.out.println("Time: " + (end - begin));
63+
preparedStatement.close();
64+
} catch (SQLException e) {
65+
e.printStackTrace();
66+
try {
67+
conn.rollback();
68+
} catch (SQLException ex) {
69+
ex.printStackTrace();
70+
}
71+
} finally {
72+
conn.close();
73+
74+
}
75+
}
76+
}

‎04fx/homework10/src/main/java/io/nononi/db/dbdemo/jdbc/JdbcBatchDemo.java‎

Lines changed: 68 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,68 @@
1+
package io.nononi.db.dbdemo.jdbc;
2+
3+
import java.sql.*;
4+
import java.util.Random;
5+
6+
/**
7+
* 批处理模式
8+
*/
9+
public class JdbcBatchDemo {
10+
public static void main(String[] args) throws Exception {
11+
Random random = new Random();
12+
Connection conn = null;
13+
try {
14+
conn = getConn();
15+
conn.setAutoCommit(false);
16+
String sql = "INSERT INTO `user` (id, name, pwd) VALUES(?, ?, ?);";
17+
PreparedStatement preparedStatement = conn.prepareStatement(sql);
18+
long begin = System.currentTimeMillis();
19+
for (int i = 0; i < 100000; i++) {
20+
preparedStatement.setInt(1, i + 1);
21+
preparedStatement.setString(2, "name_" + i);
22+
preparedStatement.setInt(3, random.nextInt(9999));
23+
preparedStatement.addBatch();
24+
if ((i + 1) % 200 == 0) {
25+
preparedStatement.executeBatch();
26+
preparedStatement.clearBatch();
27+
}
28+
}
29+
if (100000 % 200 != 0) {
30+
preparedStatement.executeBatch();
31+
preparedStatement.clearBatch();
32+
}
33+
conn.commit();
34+
long end = System.currentTimeMillis();
35+
System.out.println("Time: " + (end - begin));
36+
preparedStatement.close();
37+
} catch (SQLException e) {
38+
e.printStackTrace();
39+
try {
40+
conn.rollback();
41+
} catch (SQLException ex) {
42+
ex.printStackTrace();
43+
}
44+
} finally {
45+
conn.close();
46+
47+
}
48+
49+
}
50+
51+
private static Connection getConn() {
52+
String driver = "com.mysql.jdbc.Driver";
53+
String url = "jdbc:mysql://localhost:3306/test";
54+
String username = "root";
55+
String password = "root";
56+
Connection conn = null;
57+
try {
58+
Class.forName(driver); //classLoader,加载对应驱动
59+
conn = (Connection) DriverManager.getConnection(url, username, password);
60+
} catch (ClassNotFoundException e) {
61+
e.printStackTrace();
62+
} catch (SQLException e) {
63+
e.printStackTrace();
64+
}
65+
return conn;
66+
}
67+
68+
}

‎04fx/homework10/src/main/java/io/nononi/db/dbdemo/jdbc/JdbcDemo.java‎

Lines changed: 74 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,74 @@
1+
package io.nononi.db.dbdemo.jdbc;
2+
3+
import java.sql.*;
4+
5+
/**
6+
* JDBC增删改查，开启事务
7+
*/
8+
public class JdbcDemo {
9+
public static void main(String[] args) throws Exception {
10+
Connection conn = getConn();
11+
System.out.println("开始查询：");
12+
select(conn);
13+
insert(conn, 20, "Amy", 1234);
14+
System.out.println("插入后查询：");
15+
select(conn);
16+
update(conn, 20, "Bob", 1111);
17+
System.out.println("更新后查询：");
18+
select(conn);
19+
delete(conn, 20);
20+
System.out.println("删除后查询：");
21+
select(conn);
22+
conn.close();
23+
24+
25+
}
26+
27+
private static Connection getConn() {
28+
String driver = "com.mysql.jdbc.Driver";
29+
String url = "jdbc:mysql://localhost:3306/test";
30+
String username = "root";
31+
String password = "root";
32+
Connection conn = null;
33+
try {
34+
Class.forName(driver); //classLoader,加载对应驱动
35+
conn = (Connection) DriverManager.getConnection(url, username, password);
36+
} catch (ClassNotFoundException e) {
37+
e.printStackTrace();
38+
} catch (SQLException e) {
39+
e.printStackTrace();
40+
}
41+
return conn;
42+
}
43+
44+
private static void select(Connection connection) throws SQLException {
45+
Statement statement = connection.createStatement();
46+
ResultSet resultSet = statement.executeQuery("SELECT * FROM user");
47+
while (resultSet.next()) {
48+
System.out.print(resultSet.getInt("id") + " ");
49+
System.out.print(resultSet.getString("name") + " ");
50+
System.out.println(resultSet.getInt("pwd") + " ");
51+
}
52+
System.out.println("");
53+
}
54+
55+
private static boolean insert(Connection connection, int id, String name, int pwd) throws SQLException {
56+
Statement statement = connection.createStatement();
57+
String sql = "INSERT INTO `user` (id, name, pwd) VALUES(" + id + ",'" + name + "'," + pwd + ");";
58+
return statement.execute(sql);
59+
60+
}
61+
62+
private static boolean update(Connection connection, int id, String name, int pwd) throws SQLException {
63+
Statement statement = connection.createStatement();
64+
String sql = "UPDATE `user` SET name ='" + name + "'," + "pwd = " + pwd + " WHERE id =" + pwd + ";";
65+
return statement.execute(sql);
66+
}
67+
68+
private static int delete(Connection connection, int pwd) throws SQLException {
69+
Statement statement = connection.createStatement();
70+
String sql = "DELETE FROM `user` WHERE id=" + pwd + ";";
71+
return statement.executeUpdate(sql);
72+
}
73+
74+
}

‎04fx/homework10/src/main/java/io/nononi/db/dbdemo/jdbc/JdbcPreStmtDemo.java‎

Lines changed: 97 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,97 @@
1+
package io.nononi.db.dbdemo.jdbc;
2+
3+
import java.sql.*;
4+
5+
/**
6+
* PrepareStatement 方式改造
7+
*/
8+
public class JdbcPreStmtDemo {
9+
public static void main(String[] args) throws Exception {
10+
Connection conn = null;
11+
try {
12+
conn = getConn();
13+
conn.setAutoCommit(false);
14+
System.out.println("开始查询：");
15+
select(conn);
16+
insert(conn, 20, "Amy", 1234);
17+
System.out.println("插入后查询：");
18+
select(conn);
19+
update(conn, 20, "Bob", 1111);
20+
System.out.println("更新后查询：");
21+
select(conn);
22+
delete(conn, 20);
23+
System.out.println("删除后查询：");
24+
select(conn);
25+
conn.commit();
26+
27+
} catch (SQLException e) {
28+
e.printStackTrace();
29+
try {
30+
conn.rollback();
31+
} catch (SQLException ex) {
32+
ex.printStackTrace();
33+
}
34+
} finally {
35+
conn.close();
36+
}
37+
38+
}
39+
40+
private static Connection getConn() {
41+
String driver = "com.mysql.jdbc.Driver";
42+
String url = "jdbc:mysql://localhost:3306/test";
43+
String username = "root";
44+
String password = "root";
45+
Connection conn = null;
46+
try {
47+
Class.forName(driver); //classLoader,加载对应驱动
48+
conn = (Connection) DriverManager.getConnection(url, username, password);
49+
} catch (ClassNotFoundException e) {
50+
e.printStackTrace();
51+
} catch (SQLException e) {
52+
e.printStackTrace();
53+
}
54+
return conn;
55+
}
56+
57+
private static void select(Connection connection) throws SQLException {
58+
String sql = "SELECT * FROM user;";
59+
PreparedStatement stmt = connection.prepareStatement(sql);
60+
ResultSet resultSet = stmt.executeQuery();
61+
while (resultSet.next()) {
62+
System.out.print(resultSet.getInt("id") + " ");
63+
System.out.print(resultSet.getString("name") + " ");
64+
System.out.println(resultSet.getInt("pwd") + " ");
65+
}
66+
System.out.println("");
67+
}
68+
69+
private static int insert(Connection connection, int id, String name, int pwd) throws SQLException {
70+
String sql = "INSERT INTO `user` (id, name, pwd) VALUES(?, ?, ?);";
71+
PreparedStatement stmt = connection.prepareStatement(sql);
72+
stmt.setInt(1,id);
73+
stmt.setString(2,name);
74+
stmt.setInt(3,pwd);
75+
return stmt.executeUpdate();
76+
77+
}
78+
79+
private static int update(Connection connection, int id, String name, int pwd) throws SQLException {
80+
String sql = "UPDATE `user` SET name = ?, pwd = ? WHERE id = ?;";
81+
PreparedStatement stmt = connection.prepareStatement(sql);
82+
stmt.setString(1,name);
83+
stmt.setInt(2,pwd);
84+
stmt.setInt(3,id);
85+
return stmt.executeUpdate();
86+
}
87+
88+
private static int delete(Connection connection, int id) throws SQLException {
89+
90+
91+
String sql = "DELETE FROM `user` WHERE id=?;";
92+
PreparedStatement stmt = connection.prepareStatement(sql);
93+
stmt.setInt(1,id);
94+
return stmt.executeUpdate();
95+
}
96+
97+
}

0 commit comments

Comments
 (0)