讲到JDBC，这里面可讲的东西太多太多了，这地方主要讲几点：
1、连接数据库，connection
2、PrepareStatement
3、ResultSet
4、增删改查
5、批处理
6、数据库事务
代码参照：com.critc.JdbcTest
public class JdbcTest {
/**
* 获取数据库连接
*
* @return
*/
public Connection getConn() {
Connection conn = null;
try {
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "root");
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
/**
* 新增
*/
public void add() {
Connection conn = getConn();
String sql = "insert into staff(name) values(?)";
try {
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1, "张三");
pstmt.executeUpdate();
pstmt = null;
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/**
* 修改
*/
public void update() {
Connection conn = getConn();
String sql = "update staff set name=? where id=?";
try {
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1, "张三");
pstmt.setInt(2, 1);
pstmt.executeUpdate();
pstmt = null;
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/**
* 删除
*/
public void delete() {
Connection conn = getConn();
String sql = "delete from staff where id=?";
try {
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, 2);
pstmt.executeUpdate();
pstmt = null;
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/***
* 查询
* @return
*/
public List<Staff> query() {
List<Staff> list = new ArrayList<>();
Connection conn = getConn();
String sql = "select * from staff ";
try {
PreparedStatement pstmt = conn.prepareStatement(sql);
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
Staff staff = new Staff();
staff.setId(rs.getInt("id"));
staff.setName(rs.getString("name"));
list.add(staff);
}
pstmt = null;
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return list;
}
/**
* 批量新增
*/
public void addBatch() {
Connection conn = getConn();
String sql = "insert into staff(name) values(?)";
try {
PreparedStatement pstmt = conn.prepareStatement(sql);
for (int i = 0; i < 100; i++) {
pstmt.setString(1, "张三" + i);
pstmt.addBatch();
}
pstmt.executeBatch();
pstmt = null;
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/**
* 测试事务
*/
public void testTransaction() {
Connection conn = getConn();
String sql = "insert into staff(name) values(?)";
try {
conn.setAutoCommit(false);
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1, "张三");
pstmt.executeUpdate();
PreparedStatement pstmt2 = conn.prepareStatement(sql);
pstmt2.setString(1, "张三22");
pstmt2.executeUpdate();
conn.commit();
pstmt = null;
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void main(String[] args) {
JdbcTest jdbcTest = new JdbcTest();
Connection conn = jdbcTest.getConn();
System.out.println("数据库连接：" + conn);
jdbcTest.add();
jdbcTest.update();
jdbcTest.delete();
jdbcTest.addBatch();
List<Staff> list = jdbcTest.query();
for (Staff staff : list) {
System.out.println(staff.getName());
}
jdbcTest.testTransaction();
}
}
下面主要说几点
- sql写法，sql一定要用占位符"?"，不要直接拼写，这地方是为了防止注入式攻击，另一个原因是减少数据库解析sql时间，提高sql的执行效率。
- 在批处理时，如果一次executeBatch的量过多，比如超过3000，可以分批次执行，这样提高效率
- 事务操作，这里面是最简单的事务，如果涉及两个库之上的就不能这么写了，需要采用跨库事务来解决。
