講到JDBC，這里面可講的東西太多太多了，這地方主要講幾點：
1、連接數(shù)據(jù)庫，connection
2、PrepareStatement
3、ResultSet
4、增刪改查
5、批處理
6、數(shù)據(jù)庫事務(wù)
代碼參照：com.critc.JdbcTest
public class JdbcTest {
/**
* 獲取數(shù)據(jù)庫連接
*
* @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();
}
}
}
/**
* 測試事務(wù)
*/
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("數(shù)據(jù)庫連接：" + 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一定要用占位符"?"，不要直接拼寫，這地方是為了防止注入式攻擊，另一個原因是減少數(shù)據(jù)庫解析sql時間，提高sql的執(zhí)行效率。
- 在批處理時，如果一次executeBatch的量過多，比如超過3000，可以分批次執(zhí)行，這樣提高效率
- 事務(wù)操作，這里面是最簡單的事務(wù)，如果涉及兩個庫之上的就不能這么寫了，需要采用跨庫事務(wù)來解決。
