SUN公司為了簡化、統一對數據庫的操作，定義了一套Java操作數據庫的規范，稱之為JDBC。 JDBC全稱為：Java Data Base Connectivity（java數據庫連接），它主要由接口組成。 組成JDBC的２個包：java.sql javax.sql
開發JDBC應用需要以上2個包的支持外，還需要導入相應JDBC的數據庫實現(即數據庫驅動)。
需求：編程從user表中讀取數據，並打印在命令行窗口中。
(1) 搭建實驗環境 ：
a、在mysql中創建一個數據庫，並創建user表，同時插入數據到表中。
b、新建一個Java工程，並導入數據庫驅動。
(2) 編寫程序，在程序中加載數據庫驅動
a、方式一：DriverManager. registerDriver(Driver driver)
b、方式二：Class.forName(“com.mysql.jdbc.Driver”);
(3) 建立連接(Connection)Connection conn = DriverManager.getConnection(url,user,pass);
Statement st = conn.createStatement();
ResultSet rs = st.excuteQuery(sql);
dome：
import java.sql.*;
import com.sun.org.apache.regexp.internal.recompile;
public class dome {
/**
* @param args
*/
public static void main(String[] args) throws SQLException,ClassNotFoundException{
// TODO Auto-generated method stub
String url = "jdbc:mysql://localhost:3306/skyfin";
String username = "root";
String password = "skyfin";
//1.加載驅動
//DriverManager.registerDriver(new com.mysql.jdbc.Driver());
Class.forName("com.mysql.jdbc.Driver"); //2獲取連接
Connection connection = DriverManager.getConnection(url,username,password);
//3.獲取向數據庫發sql語句的statament對象
Statement stat = connection.createStatement();
//4.向數據庫發送sql，獲取數據庫返回的結果集
ResultSet rsResultSet = stat.executeQuery("select * from user");
//5.從結果集中獲取數據while (rsResultSet.next()) {System.out.println("id = "+ rsResultSet.getObject("id"));System.out.println("name = "+ rsResultSet.getObject("name"));
System.out.println("password = "+ rsResultSet.getObject("password"));}
//6.釋放資源（釋放鏈接）rsResultSet.close();stat.close();connection.close();}}
Jdbc程序中的DriverManager用於加載驅動，並創建與數據庫的鏈接，這個API的常用方法：
DriverManager.registerDriver(new Driver());
DriverManager.getConnection(url, user, password);
注意：在實際開發中並不推薦采用registerDriver方法注冊驅動。原因有二：Class.forName(“com.mysql.jdbc.Driver”);采用此種方式不會導致驅動對象在內存中重復出現，並且采用此種方式，程序僅僅只需要一個字符串，不需要依賴具體的驅動，使程序的靈活性更高。
URL用於標識數據庫的位置，程序員通過URL地址告訴JDBC程序連接哪個數據庫，
MySql 數據庫的URL寫法為： jdbc:mysql:［］//localhost:3306/test ?參數名：參數值
常用數據庫URL地址的寫法：
Oracle:jdbc:oracle:thin:@localhost:1521:skyfin
SqlServer:jdbc:microsoft:sqlserver://localhost:1433; DatabaseName=skyfin
MySql:jdbc:mysql://localhost:3306/skyfin
Mysql的url地址的簡寫形式： jdbc:mysql://skyfin
常用屬性：useUnicode=true&characterEncoding=UTF-8
jdbc:mysql://localhost:3306/test?user=root&password=&useUnicode=true&characterEncoding=gbk&autoReconnect=true&failOverReadOnly
public static void main(String[] args) throws SQLException,ClassNotFoundException{
// TODO Auto-generated method stub
String url = "jdbc:mysql://localhost:3306/skyfin";
String username = "root";
String password = "skyfin";
//DriverManager.registerDriver(new com.mysql.jdbc.Driver());
Class.forName("com.mysql.jdbc.Driver");
Connection connection = DriverManager.getConnection(url,username,password);
Statement stat = connection.createStatement();
/*
* 執行查找操作
*/
ResultSet rsResultSet = stat.executeQuery("select * from user");
while (rsResultSet.next()) {
System.out.println("id = "+ rsResultSet.getObject("id"));
System.out.println("name = "+ rsResultSet.getObject("name"));
System.out.println("password = "+ rsResultSet.getObject("password"));
}
/*
* 執行插入操作
*/
String sql = "insert into user(id,name,password) value(6,"+"'staff'"+","+"'staff'"+")";
System.out.println(sql);
int statentnum = stat.executeUpdate(sql);
if (statentnum>0) {
System.out.println("insert ok");
}
/*
* 執行更新操作
*/
sql = "update user set name = 'skstaff' where name = 'staff'";
System.out.println(sql);
statentnum = stat.executeUpdate(sql);
if (statentnum>0) {
System.out.println("update ok");
}
/*
* 執行刪除操作
*/
sql = "delete from user where name = 'skstaff'";
System.out.println(sql);
statentnum = stat.executeUpdate(sql);
if (statentnum>0) {
System.out.println("delete ok");
}
/*
* 資源的釋放
*/
rsResultSet.close();
stat.close();
connection.close();
}
public static void main(String[] args) throws SQLException,ClassNotFoundException{
// TODO Auto-generated method stub
String url = "jdbc:mysql://localhost:3306/skyfin";
String username = "root";
String password = "skyfin";
//DriverManager.registerDriver(new com.mysql.jdbc.Driver());
Class.forName("com.mysql.jdbc.Driver");
Connection connection = DriverManager.getConnection(url,username,password);
String sql = "insert into user1(id,name) value(?,?)";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
for(int i = 0;i<10000;i++){
preparedStatement.setInt(1, i);
preparedStatement.setString(2, "skyfin"+i);
preparedStatement.executeUpdate();
}
}
public static void main(String[] args) throws SQLException,ClassNotFoundException{
// TODO Auto-generated method stub
String url = "jdbc:mysql://localhost:3306/skyfin";
String username = "root";
String password = "skyfin";
//DriverManager.registerDriver(new com.mysql.jdbc.Driver());
Class.forName("com.mysql.jdbc.Driver");
Connection connection = DriverManager.getConnection(url,username,password);
String sql = "insert into user1(id,name) value(?,?)";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
for(int i = 0;i<10000;i++){
preparedStatement.setInt(1, i);
preparedStatement.setString(2, "skyfin"+i);
//preparedStatement.executeUpdate();
/*
* 使用executeBatch()
*/
preparedStatement.addBatch();
}
//執行批處理
preparedStatement.executeBatch();
}
注意：1. 如果使用了 addBatch() -> executeBatch() 還是很慢，那就得使用到這個參數了rewriteBatchedStatements=true (啟動批處理操作)
public static void main(String[] args) throws SQLException,ClassNotFoundException{
// TODO Auto-generated method stub
String url = "jdbc:mysql://localhost:3306/skyfin";
String username = "root";
String password = "skyfin";
//DriverManager.registerDriver(new com.mysql.jdbc.Driver());
Class.forName("com.mysql.jdbc.Driver");
Connection connection = DriverManager.getConnection(url,username,password);
//關閉自動提交
connection.setAutoCommit(false);
String sql = "update user1 set name = ?where id = ?";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
for(int i = 0;i<10000;i++){
preparedStatement.setString(1, "loco"+i);
preparedStatement.setInt(2, i);
//preparedStatement.executeUpdate();
/*
* 使用executeBatch()
*/
preparedStatement.addBatch();
}
//執行批處理
preparedStatement.executeBatch();
preparedStatement.close();
//執行完後手動提交事務
connection.commit();
//打開自動提交
connection.setAutoCommit(true);
connection.close();
}
public static void main(String[] args) throws SQLException,ClassNotFoundException{
// TODO Auto-generated method stub
String url = "jdbc:mysql://localhost:3306/skyfin";
String username = "root";
String password = "skyfin";
//DriverManager.registerDriver(new com.mysql.jdbc.Driver());
Class.forName("com.mysql.jdbc.Driver");
Connection connection = DriverManager.getConnection(url,username,password);
//關閉自動提交
connection.setAutoCommit(false);
String sql = "update user1 set name = ?where id = ?";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
for(int i = 0;i<10000;i++){
preparedStatement.setString(1, "skyfin"+i);
preparedStatement.setInt(2, i);
//preparedStatement.executeUpdate();
/*
* 使用executeBatch()
*/
if (i>0&&i%500 == 0) {
preparedStatement.executeBatch();
//如果不想出錯後，完全沒保留數據，則可以沒執行一次提交一次，但得保證數據不會重復
connection.commit();
}
preparedStatement.addBatch();
}
//執行批處理
preparedStatement.executeBatch();
preparedStatement.close();
//執行完後手動提交事務
connection.commit();
//打開自動提交
connection.setAutoCommit(true);
connection.close();
}
public ListgetAll(){ Connection conn = null; PreparedStatement st = null; ResultSet rs = null; try{ conn = JdbcUtils.getConnection(); String sql = "select * from customer"; st = conn.prepareStatement(sql); rs = st.executeQuery(); List list = new ArrayList(); while(rs.next()){ Customer c = new Customer(); c.setBirthday(rs.getDate("birthday")); c.setCellphone(rs.getString("cellphone")); c.setDescription(rs.getString("description")); c.setEmail(rs.getString("email")); c.setGender(rs.getString("gender")); c.setId(rs.getString("id")); c.setName(rs.getString("name")); c.setPreference(rs.getString("preference")); c.setType(rs.getString("type")); list.add(c); } return list; }catch (Exception e) { throw new DaoException(e); }finally{ JdbcUtils.release(conn, st, rs); } }
