Javaとデータベース——JDBC標準
10737 ワード
JDBC
由来と説明 , API, API , , ,
Sum , —— JDBC , JDBC , Java ,
JDBC Java web , ( )
JDBC
常用四大API :
1、
Class.forName("com.jdbc.mysql.Driver");
2、
Connection conn = DriverManager.getConnection();
:
1、 SQL (3 )
Statement createStatement() // SQL
PreparedStatement prepareStatement() // SQL
CallableStatement prepareCall() // SQL
2、
setAutoCommit(boolean b)
commit()
rollback()
:
1、 SQL
boolean execute(String sql) // SQL , select true, false
ResultSet executeQuery(String sql) // SQL select
int executeUpdate(String sql) // SQL insert/update/delete
2、 SQL
addBatch(String sql) //
executeBatch() //
clearBatch() //
(select )
:
next() //
getXXX() //
getObject() //
JDBCリソース解放
解放の原則:
,
;資源が希少で、放出しないとすぐにブロックされますConnection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb","root","root");
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("select * from user");
rs.close();
stmt.close();
conn.close();
JDBC CURD操作
Connection conn = null;
Statement stmt = null;
try{
//
Class.forName("com.mysql.jdbc.Driver");
//
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/myDB","root","root");
// SQL
stmt = conn.createStatement();
// SQL
String sql = "insert into user values ("xiaomin"," ",12)";
// SQL
int i = stmt.executeUpdate(sql);
if(i > 0){
System.out.println(" !");
}
}catch(Exception e){
e.printStackTrace();
}finally{
if(stmt != null){
try{
stmt .close();
}catch(SQLException e){
e.printStackTrace();
}finally{
stmt = null;
}
}
if(conn != null){
try{
conn.close();
}catch(SQLException e){
e.printStackTrace();
}finally{
conn = null;
}
}
}
Connection conn = null;
Statement stmt = null;
try{
//
Class.forName("com.mysql.jdbc.Driver");
//
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/myDB","root","root");
// SQL
stmt = conn.createStatement();
// SQL
String sql = "update user set name = 'wt'";
// SQL
int i = stmt.executeUpdate(sql);
if(i > 0){
System.out.println(" !");
}
}catch(Exception e){
e.printStackTrace();
}finally{
if(stmt != null){
try{
stmt .close();
}catch(SQLException e){
e.printStackTrace();
}finally{
stmt = null;
}
}
if(conn != null){
try{
conn.close();
}catch(SQLException e){
e.printStackTrace();
}finally{
conn = null;
}
}
}
Connection conn = null;
Statement stmt = null;
try{
//
Class.forName("com.mysql.jdbc.Driver");
//
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/myDB","root","root");
// SQL
stmt = conn.createStatement();
// SQL
String sql = "delete from user where id = 3";
// SQL
int i = stmt.executeUpdate(sql);
if(i > 0){
System.out.println(" !");
}
}catch(Exception e){
e.printStackTrace();
}finally{
if(stmt != null){
try{
stmt .close();
}catch(SQLException e){
e.printStackTrace();
}finally{
stmt = null;
}
}
if(conn != null){
try{
conn.close();
}catch(SQLException e){
e.printStackTrace();
}finally{
conn = null;
}
}
}
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try{
//
Class.forName("com.mysql.jdbc.Driver");
//
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/myDB","root","root");
// SQL
stmt = conn.createStatement();
// SQL
String sql = "select * from user";
// SQL
rs = stmt.executeQuery(sql);
if(rs.next()){
System.out.print(rs.getString("name"));
}
}catch(Exception e){
e.printStackTrace();
}finally{
if(rs != null){
try{
rs .close();
}catch(SQLException e){
e.printStackTrace();
}finally{
rs = null;
}
}
if(stmt != null){
try{
stmt .close();
}catch(SQLException e){
e.printStackTrace();
}finally{
stmt = null;
}
}
if(conn != null){
try{
conn.close();
}catch(SQLException e){
e.printStackTrace();
}finally{
conn = null;
}
}
}
JDBC SQL注入ソリューション
開発時、
PreparedStatement
の代わりにStatement
を使用してSQLを実行し、 SQL
Connection conn = null;
PreparedStatement pstmt = null;
try{
//
Class.forName("com.mysql.jdbc.Driver");
//
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/myDB","root","root");
// SQL
String sql = "insert into user values (?,?,?)";
// SQL
pstmt = conn.preparedStatement(sql);
//
pstmt.setString(1, "qqq");
pstmt.setString(2, "bbb");
pstmt.setString(3, "ccc");
// SQL
int i = pstmt.executeUpdate();
if(i > 0){
System.out.println(" !");
}
}catch(Exception e){
e.printStackTrace();
}finally{
if(pstmt != null){
try{
pstmt.close();
}catch(SQLException e){
e.printStackTrace();
}finally{
pstmt = null;
}
}
if(conn != null){
try{
conn.close();
}catch(SQLException e){
e.printStackTrace();
}finally{
conn = null;
}
}
}
Connection conn = null;
PreparedStatement pstmt = null;
try{
//
Class.forName("com.mysql.jdbc.Driver");
//
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/myDB","root","root");
// SQL
String sql = "update user set name = ?, age = ?, pwd = ? where id = ?";
// SQL
pstmt = conn.preparedStatement(sql);
//
pstmt.setString(1, "wt");
pstmt.setString(2, 15);
pstmt.setString(3, "basdcx");
pstmt.setString(4, 10);
// SQL
int i = pstmt.executeUpdate();
if(i > 0){
System.out.println(" !");
}
}catch(Exception e){
e.printStackTrace();
}finally{
if(pstmt != null){
try{
pstmt.close();
}catch(SQLException e){
e.printStackTrace();
}finally{
pstmt = null;
}
}
if(conn != null){
try{
conn.close();
}catch(SQLException e){
e.printStackTrace();
}finally{
conn = null;
}
}
}
Connection conn = null;
PreparedStatement pstmt = null;
try{
//
Class.forName("com.mysql.jdbc.Driver");
//
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/myDB","root","root");
// SQL
String sql = "delete from user where id = ?";
// SQL
pstmt = conn.preparedStatement(sql);
//
pstmt.setString(1, 16);
// SQL
int i = pstmt.executeUpdate();
if(i > 0){
System.out.println(" !");
}
}catch(Exception e){
e.printStackTrace();
}finally{
if(pstmt != null){
try{
pstmt.close();
}catch(SQLException e){
e.printStackTrace();
}finally{
pstmt = null;
}
}
if(conn != null){
try{
conn.close();
}catch(SQLException e){
e.printStackTrace();
}finally{
conn = null;
}
}
}
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try{
//
Class.forName("com.mysql.jdbc.Driver");
//
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/myDB","root","root");
// SQL
String sql = "select * from user where sex = ?";
// SQL
pstmt = conn.preparedStatement(sql);
//
pstmt.setString(1, ' ');
// SQL
rs = pstmt.executeQuery();
while(rs.next()){
System.out.println(rs.getString("name"));
}
}catch(Exception e){
e.printStackTrace();
}finally{
if(rs != null){
try{
rs.close();
}catch(SQLException e){
e.printStackTrace();
}finally{
rs = null;
}
}
if(pstmt != null){
try{
pstmt.close();
}catch(SQLException e){
e.printStackTrace();
}finally{
pstmt = null;
}
}
if(conn != null){
try{
conn.close();
}catch(SQLException e){
e.printStackTrace();
}finally{
conn = null;
}
}
}
接続プール(データソース)
JDBC
データベース接続は頻繁に作成され、消費され、リソースの使用に無駄です.C3P0、HikariCP、Druid、Tomcat、Dbcp
ComboPooledDataSource dataSource = new ComboPooledDataSource(); //
//
Connection conn = dataSource.getConnection();