封装JDBC连接数据库
public class JDBCUtils {
private static String url;
private static String username;
private static String password;
private JDBCUtils() {
}
static {
try {
Properties properties = new Properties();
properties.load(new FileReader("mysqlLogin.properties"));
username = properties.getProperty("username");
password = properties.getProperty("password");
url = properties.getProperty("url");
String driverClassName = properties.getProperty("driverClassName");
Class.forName(driverClassName);
} catch (ClassNotFoundException | FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(url, username, password);
}
public static void close(Connection conn, Statement statement, ResultSet resultSet) throws SQLException {
if (conn != null) {
conn.close();
}
if (statement != null) {
statement.close();
}
if (resultSet != null) {
resultSet.close();
}
}
public static void close(Connection conn, Statement statement) throws SQLException {
if (conn != null) {
conn.close();
}
if (statement != null) {
statement.close();
}
}
}
批处理
概念讲述
在调用prepareStatement.executebatch();代码时,
开启rewriteBatchedStatements会成倍提高执行效率
不开启则是单条sql提交
开启则是多条sql提交
但提交的sql不能超过max_allowed_packet的设置值 不然会报错
查询max_allowed_packet方法
进入mysql容器 运行
mysql> show variables like 'max_allowed_packet';
+--------------------+---------+
| Variable_name | Value |
+--------------------+---------+
| max_allowed_packet | 4194304 |
+--------------------+---------+
max_allowed_packet的单位为字节。
插入大量数据时 建议使用批处理来做
statement.addBatch();//添加批处理,先将数据缓存起来
statement.executeBatch();//执行批处理
statement.clearBatch();//清空缓存
代码实现
public class JDBCMore {
public static void main(String[] args) throws Exception {
ArrayList<User> list = new ArrayList<>();
for (int i = 0; i < 20000; i++) {
User user = new User("测试数据" + i, "123456");
list.add(user);
}
Connection conn = JDBCUtils.getConnection();
String sql = "insert into user(username, password) values(?,?)";
PreparedStatement preparedStatement = conn.prepareStatement(sql);
for (User user : list) {
preparedStatement.setString(1, user.getUsername());
preparedStatement.setString(2, user.getPassword());
preparedStatement.addBatch();
}
preparedStatement.executeBatch();
preparedStatement.clearBatch();
JDBCUtils.close(conn, preparedStatement);
}
}
JDBC调用存储过程和函数
调用存储过程
调用存储过程 {call <procedure-name>[(<arg1>,<arg2>, ...)]}
public class JDBCPro {
public static void main(String[] args) throws Exception {
Connection conn = JDBCUtils.getConnection();
CallableStatement callableStatement
= conn.prepareCall("{call myPro(?,?)}");
callableStatement.setString(1,"hehe");
callableStatement.registerOutParameter(2, Types.INTEGER);
callableStatement.execute();
int anInt = callableStatement.getInt(2);
System.out.println(anInt);
JDBCUtils.close(conn,callableStatement);
}
}
调用函数
调用自定义函数 {?=call<procedure -name >[( < arg1 >,<arg2 >, ...)]}
public class JDBCFun {
public static void main(String[] args) throws Exception {
Connection conn = JDBCUtils.getConnection();
CallableStatement callableStatement
= conn.prepareCall("{?=call md5(?)}");
callableStatement.setString(2, "123456");
callableStatement.registerOutParameter(1, Types.VARCHAR);
callableStatement.execute();
String str = callableStatement.getString(1);
System.out.println("结果是: " + str);
JDBCUtils.close(conn,callableStatement);
}
}
获取自增长键的值
概念讲述
1.要获取自增长键的值 需要在获取操作对象时声明一个参数 Statement.RETURN_GENERATED_KEYS
PreparedStatement preparedStatement
= conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
2.当数据插入成功后 就可以取出这个自增长键的值
ResultSet generatedKeys = preparedStatement.getGeneratedKeys();
while (generatedKeys.next()){
keyValue = generatedKeys.getInt(1);
}
3.你在其他表中就可以使用这个自增长键的值
INSERT INTO mylog(id, content, mydate) VALUES (NULL,'hehehe',NULL)
SELECT LAST_INSERT_ID(); -- 这个函数也可以获取最后新增这条数据的自增长键的值
代码实现
public class JDBCKeys {
public static void main(String[] args) throws SQLException {
Connection conn = JDBCUtils.getConnection();
String sql = "insert into user(username,password) values (?,?)";
PreparedStatement preparedStatement
= conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
preparedStatement.setString(1,"abc");
preparedStatement.setString(2,"123456");
int i = preparedStatement.executeUpdate();
ResultSet generatedKeys = null;
if (i > 0) {
System.out.println("插入成功");
generatedKeys = preparedStatement.getGeneratedKeys();
int id = 0;
while (generatedKeys.next()) {
id = generatedKeys.getInt(1);
}
System.out.println(id);
} else {
System.out.println("插入失败");
}
JDBCUtils.close(conn, preparedStatement, generatedKeys);
}
}