操作数据库的方式包括CRUD,其中大部分都比较简单,唯一需要关注的可能是获取自增id的用法。
下面我们将以一一举例来说明这些用法。
1.获取连接
2.操作数据库
1.CRUD例子
package com.bsx.test;
import com.bsx.test.db.ConnManager;
import org.junit.Test;
import java.sql.*;
/**
* @Description:
* @author: ztd
* @date 2019/7/8 下午4:41
*/
public class ConnectionCRUDTest {
/**
* 新增
* @throws Exception
*/
@Test
public void testCreate() throws Exception {
Connection connection = ConnManager.initMysql();
String sql = "insert into test (name,create_time) values (?,?)";
PreparedStatement statement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
statement.setString(1, "p1");
statement.setTimestamp(2, new Timestamp(new java.util.Date().getTime()));
statement.executeUpdate();
ResultSet resultSet = statement.getGeneratedKeys();
resultSet.next();
int key = resultSet.getInt(1);
// 获取自增id
System.out.println(key);
resultSet.close();
statement.close();
connection.close();
}
/**
* 删除
* @throws Exception
*/
@Test
public void testDelete() throws Exception {
Connection connection = ConnManager.initMysql();
String sql = "DELETE FROM test WHERE id=?";
PreparedStatement statement = connection.prepareStatement(sql);
statement.setLong(1, 6);
int affectRows = statement.executeUpdate();
System.out.println(affectRows);
statement.close();
connection.close();
}
/**
* 更新
* @throws Exception
*/
@Test
public void testUpdate() throws Exception {
Connection connection = ConnManager.initMysql();
String sql = "update test set name = ? where id=?";
PreparedStatement statement = connection.prepareStatement(sql);
statement.setString(1, "hehe");
statement.setLong(2, 1);
int affectRows = statement.executeUpdate();
System.out.println(affectRows);
}
/**
* 查询
* @throws Exception
*/
@Test
public void testRetrieve() throws Exception {
Connection connection = ConnManager.initMysql();
String sql = "SELECT * from test where id>?";
PreparedStatement statement = connection.prepareStatement(sql);
statement.setLong(1, 1);
ResultSet resultSet = statement.executeQuery();
while (resultSet.next()) {
Long id = resultSet.getLong(1);
String name = resultSet.getString(2);
Timestamp createTime = resultSet.getTimestamp(3);
System.out.println("id: " + id + " name:" + name + " create_time:" + createTime);
}
resultSet.close();
statement.close();
connection.close();
}
}
2.执行存储过程
package com.bsx.test;
import com.bsx.test.db.ConnManager;
import org.junit.Test;
import java.sql.*;
/**
* @Description:
* @author: ztd
* @date 2019/7/8 下午4:41
*/
public class ConnectionTest {
@Test
public void execFunction() throws Exception {
Connection connection = ConnManager.initMysql();
String result;
CallableStatement statement = connection.prepareCall("{ call creat_cal_report_data(?,?,?)}");
statement.setString(1, "name");
statement.setString(2, "age");
statement.registerOutParameter(3,java.sql.Types.VARCHAR);
statement.executeQuery();
result = (String) statement.getObject(3);
System.out.println(result);
statement.close();
connection.close();
}
}
3.批量操作例子
package com.dada.test;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.Statement;
public class TestBatch {
public static void main(String[] args) throws Exception {
// insertByPreparedStatement();
insertByBatchStat();
// insertByStat();
}
public static Connection getConnection() {
Connection conn = null;
try {
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/test?characterEncoding=utf-8";
conn= DriverManager.getConnection(url, "root", "test");
} catch (Exception e) {
e.printStackTrace();
}
return conn;
}
/**
* 通过PreparedStatement执行batch
* @throws Exception
*/
public static void insertByPreparedStatement() throws Exception {
Connection conn = getConnection();
conn.setAutoCommit(false);
String sql = "insert into test(id,name) values(?,?)";
PreparedStatement ps = conn.prepareStatement(sql);
try{
for(int i=1;i<10;i++) {
// 如果要为某个字段赋予空值,就使用下面的方法,而这个地方也
// ps.setNull(4,Types.CHAR);
ps.setInt(1, i);
ps.setString(2, "test"+i);
ps.addBatch();
}
ps.executeBatch();
conn.commit();
} catch(Exception e) {
try{
conn.rollback();
}catch(Exception e1) {
e1.printStackTrace();
}
e.printStackTrace();
} finally {
try{
ps.close();
conn.close();
}catch(Exception e) {
e.printStackTrace();
}
}
}
/**
* 使用批量插入
* @throws Exception
*/
public static void insertByBatchStat() throws Exception {
Connection conn = getConnection();
conn.setAutoCommit(false);
Statement ps = conn.createStatement();
try{
for(int i=1;i<10;i++) {
ps.addBatch("insert into test(id,name) values("+i+",'test"+i+"')");
}
ps.executeBatch();
conn.commit();
} catch(Exception e) {
try{
conn.rollback();
}catch(Exception e1) {
e1.printStackTrace();
}
e.printStackTrace();
} finally {
try{
ps.close();
conn.close();
}catch(Exception e) {
e.printStackTrace();
}
}
}
/**
* 直接用Statement插入
* @throws Exception
*/
public static void insertByStat() throws Exception {
Connection conn = getConnection();
conn.setAutoCommit(false);
Statement ps = conn.createStatement();
try{
for(int i=1;i<10;i++) {
ps.execute("insert into test(id,name) values("+i+",'test"+i+"')");
}
ps.executeBatch();
conn.commit();
} catch(Exception e) {
try{
conn.rollback();
}catch(Exception e1) {
e1.printStackTrace();
}
e.printStackTrace();
} finally {
try{
ps.close();
conn.close();
}catch(Exception e) {
e.printStackTrace();
}
}
}
}
4.查询一张表是不是存在
@Test
public void testTableExists() throws Exception {
Connection connection = ConnManager.initMysql();
String sql = "show tables from test like 'test'";
PreparedStatement ps = connection.prepareStatement(sql);
ResultSet rs = ps.executeQuery();
boolean isTableExist = rs.first();
System.out.println(isTableExist);
ps.close();
connection.close();
}