您好,登錄后才能下訂單哦!
這期內(nèi)容當(dāng)中小編將會給大家?guī)碛嘘P(guān)使用JDBC怎么連接MySql數(shù)據(jù)庫并實現(xiàn)查詢、插入、刪除、更新等操作,文章內(nèi)容豐富且以專業(yè)的角度為大家分析和敘述,閱讀完這篇文章希望大家可以有所收獲。
一.JDBC連接數(shù)據(jù)庫(編輯)步驟(主要有六個步驟)
1.注冊驅(qū)動: Class.forName("com.mysql.jdbc.Driver");顯示的加載到JVM中
2.獲取連接:(1) param1: 要連接數(shù)據(jù)庫的url-----》 String url="jdbc:mysql://localhost:3306/test?"+ "useUnicode=true&characterEncoding=UTF8";//防止亂碼
param2:要連接數(shù)據(jù)庫的用戶名--》 String user="h5";
param3:要連接數(shù)據(jù)庫的密碼----》 String pass="111";
Connection conn=DriverManager.getConnection(url,user,pass);//DriverManager下的方法:getConnection(String url,String username,String password)
(2)接下來我們分析下url:"jdbc(這是協(xié)議以jdbc開頭):mysql(這是子協(xié)議,數(shù)據(jù)庫管理系統(tǒng)名稱)://localhost(數(shù)據(jù)庫來源地址):3306(目標(biāo)端口)/test(要查詢的表)?"
"useUnicode=true&characterEncoding=UTF8";添加這個是為了防止亂碼,指定使用Unicode字符集 ,且使用UTF-8來編輯。
3.創(chuàng)建一個Statement語句對象(主要三種方法): Statement stmt=conn.createStatement();//Connection接口下的方法:Statement createStatement()
PreparedStatement pstmt = conn.PreparedStatement() ;
CallableStatement cstmt = conn.prepareCall("{CALL demoSp(? , ?)}") ;
下面我們來分析下他們:(1) Statement與 PreparedStatement對象的區(qū)別,后者可以動態(tài)設(shè)置查詢參數(shù)
(2)設(shè)置參數(shù)的方法 PreparedStatement.setXXXX(parameterIndex,value),如果數(shù)據(jù)庫參數(shù)類型是varchar 則用setString,如果參數(shù)類型是Integer 則用setInt
(3)CallableStatement.setXXXX(parameterIndex,value) //按照參數(shù)的順序設(shè)置value
CallableStatement.setXXXX(parameterName,value) //按照參數(shù)的名字來設(shè)置value,這個名字是在定義存儲過程的時候的形式參數(shù)的名字
(4)CallableStatement.registerOutParameter方法用于聲明一個存儲過程輸出類型的參數(shù),用以接收存儲過程的輸出值
4.執(zhí)行SQL語句: ResultSet rs=stmt.executeQuery(Sql);除了查詢語句是executeQuery();其他全部是executeUpdate();
Statement接口下的方法:
boolean execute(String sql):執(zhí)行SQL語句,如果返回值是結(jié)果集則為true,否則為false
ResultSet executeQuery(String sql):執(zhí)行SQL語句,返回值為ResultSet
int executeUpdate(String sql):執(zhí)行SQL語句,返回值為所影響的行數(shù)
5.處理結(jié)果集:ResultSet對象的getXxxx方法,取決于數(shù)據(jù)庫中表的字段的類型,例如:varchar2 對應(yīng)方法是getString ,如果是 integer 對應(yīng)方法是getInt/getLong
While(rs.next()){ rs.getInt(columnIndex); //通過列的序號來獲取字段的值 rs.getString(columnName);//通過列的名字來獲取字段的值 ResultSet接口下常見的方法:beforeFirst();將游標(biāo)移動到ResultSet中第一條記錄(的前面) afterLast();將游標(biāo)移動到ResultSet中最后一條記錄(的后面) absolute(intcolumn):將游標(biāo)移動到相對于第一行的指定行,負(fù)數(shù)則為相對于最后一條記錄 previous():將游標(biāo)上移一行 next():將游標(biāo)下移一行 ResultSet.TYPE_SCROLL_SENSITIVE 結(jié)果集可以滾動 ResultSet.CONCUR_READ_ONLY 結(jié)果集只讀,不能修改
6.關(guān)閉資源: 操作完以后要關(guān)閉jdbc來釋放jdbc資源。但是順序要和你定義對象的時候相反,就像關(guān)門一樣,從里面先關(guān),一直往外關(guān)。
如下示例:
if(rs !=null){//1.關(guān)閉結(jié)果集 try{ rs.close(); } catch (SQLException e){ e.printStackTrace(); } } if(stmt !=null){//2.關(guān)閉聲明的對象 try{ stmt.close(); }catch(SQLException e){ e.printStackTrace(); } } if(conn !=null){//3.關(guān)閉連接 (記住一定要先關(guān)閉前面的1.2.然后在關(guān)閉連接) try{ conn.close(); }catch(Exception e){ e.printStackTrace(); } }
二.下面是一個簡單的(詳細(xì)的)查詢數(shù)據(jù)的例子
package javacore1;//這是你建的一個包名。 import java.sql.CallableStatement; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; //左邊這五個是你在寫程序時要導(dǎo)入的包名,記住是要在java.sql下的包。 import java.sql.SQLException; import java.sql.Statement; public class jdbc { //定義一個類 public static void main(String[] args){ //主方法 try{ String driver="com.mysql.jdbc.Driver"; //1.定義驅(qū)動程序名為driver內(nèi)容為com.mysql.jdbc.Driver String url="jdbc:mysql://localhost:3306/test?" //2.定義url;jdbc是協(xié)議;mysql是子協(xié)議:表示數(shù)據(jù)庫系統(tǒng)管理名稱;localhost:3306是你數(shù)據(jù)庫來源的地址和目標(biāo)端口;test是我本人建的表位置所在處,你以你的為標(biāo)準(zhǔn)。 + "useUnicode=true&characterEncoding=UTF8"; //防止亂碼;useUnicode=true表示使用Unicode字符集;characterEncoding=UTF8表示使用UTF-8來編輯的。 String user="h5"; //3.定義用戶名,寫你想要連接到的用戶。 String pass="111"; //4.用戶密碼。 String querySql="select * from employees"; //5.你想要查找的表名。 Class.forName(driver); //6.注冊驅(qū)動程序,用java.lang包下面的class類里面的Class.froName();方法 此處的driver就是1里面定義的driver,也可以 Class.forName("com.mysql.jdbc.Driver"); Connection conn=DriverManager.getConnection(url,user,pass);//7.獲取數(shù)據(jù)庫連接,使用java.sql里面的DriverManager的getConnectin(String url , String username ,String password )來完成 //括號里面的url,user,pass便是前面定義的2,3,4步驟內(nèi)容; Statement stmt=conn.createStatement(); //8.構(gòu)造一個statement對象來執(zhí)行sql語句:主要有Statement,PreparedStatement,CallableStatement三種實例來實現(xiàn) // 三種實現(xiàn)方法分別為:Statement stmt = con.createStatement() ; // PreparedStatement pstmt = conn.prepareStatement(sql) ; // CallableStatement cstmt = conn.prepareCall("{CALL demoSp(? , ?)}") ; ResultSet rs=stmt.executeQuery(querySql);//9.執(zhí)行sql并返還結(jié)束 ;ResultSet executeQuery(String sqlString):用于返還一個結(jié)果集(ResultSet)對象。 while(rs.next()){ //10.遍歷結(jié)果集 System.out.println("人員編號:"+rs.getString("employee_id")+"工資:"+rs.getString("salary")+"姓名:"+rs.getString("last_name"));//使用getString()方法獲取你表里的資料名 } if(rs !=null){//11.關(guān)閉記錄集 try{ rs.close(); } catch (SQLException e){ e.printStackTrace(); } } if(stmt !=null){//12.關(guān)閉聲明的對象 try{ stmt.close(); }catch(SQLException e){ e.printStackTrace(); } } if(conn !=null){//13.關(guān)閉連接 (記住一定要先關(guān)閉前面的11.12.然后在關(guān)閉連接,就像關(guān)門一樣,先關(guān)里面的,最后關(guān)最外面的) try{ conn.close(); }catch(SQLException e){ e.printStackTrace(); } } }catch(Exception e){ e.printStackTrace(); } } }
三.為了更加直觀,我們把獲取數(shù)據(jù)庫連接和釋放連接封裝在方法里,以便于后面更好的操作
package javacore1; import java.sql.Connection; import java.sql.DriverManager; public class jdbc{ public static void main(String[] args){ Connection conn= getConnection("h5", "111"); //獲取數(shù)據(jù)庫連接 /*,,,,此處為方法名來獲取連接,例如 query(conn),,,,,*/ releaseConnection(conn); // 釋放數(shù)據(jù)庫連接 } /*,,,,,,,,,,此處構(gòu)建一個你想要的功能的方法,,,,,,,,,,,,*/ //數(shù)據(jù)庫連接 public static Connection getConnection(String user, String pass) { Connection conn = null;//聲明連接對象 String driver = "com.mysql.jdbc.Driver";// 驅(qū)動程序類名 String url = "jdbc:mysql://localhost:3306/test?" // 數(shù)據(jù)庫URL + "useUnicode=true&characterEncoding=UTF8";// 防止亂碼 try { Class.forName(driver);// 注冊(加載)驅(qū)動程序 conn = DriverManager.getConnection(url, user, pass);// 獲取數(shù)據(jù)庫連接 } catch (Exception e) { e.printStackTrace(); } return conn; } // 釋放數(shù)據(jù)庫連接 public static void releaseConnection(Connection conn) { try { if (conn != null) conn.close(); } catch (Exception e) { e.printStackTrace(); } } }
四.實現(xiàn)查詢、插入、刪除、更新等十一個處理數(shù)據(jù)庫信息的功能
1.查詢數(shù)據(jù)
package javacore1; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class Query { public static void main(String[] args) { Connection conn = getConnection("h5", "111");// 獲取數(shù)據(jù)庫連接 query(conn); //方法名調(diào)用數(shù)據(jù)庫連接 releaseConnection(conn);//釋放數(shù)據(jù)庫連接 } //查詢數(shù)據(jù),定義的query方法 public static void query(Connection conn){ String Sql="select * from employees"; try{ Statement stmt=conn.createStatement(); //也可以使用PreparedStatement來做 ResultSet rs=stmt.executeQuery(Sql);//執(zhí)行sql語句并返還結(jié)束 while(rs.next()){//遍歷結(jié)果集 ,向下一行 System.out.println("人員編號:"+rs.getString("employee_id")+"工資:"+rs.getString("salary")); } if(rs !=null){ try{ rs.close(); } catch (SQLException e){ e.printStackTrace(); } } if(stmt !=null){ try{ stmt.close(); }catch(SQLException e){ e.printStackTrace(); } } if(conn !=null){ try{ conn.close(); }catch(SQLException e){ e.printStackTrace(); } } }catch(Exception e){ e.printStackTrace(); } } //數(shù)據(jù)庫連接 public static Connection getConnection(String user, String pass) { Connection conn = null;//聲明連接對象 String driver = "com.mysql.jdbc.Driver";// 驅(qū)動程序類名 String url = "jdbc:mysql://localhost:3306/test?" // 數(shù)據(jù)庫URL + "useUnicode=true&characterEncoding=UTF8";// 防止亂碼 try { Class.forName(driver);// 注冊(加載)驅(qū)動程序 conn = DriverManager.getConnection(url, user, pass);// 獲取數(shù)據(jù)庫連接 } catch (Exception e) { e.printStackTrace(); } return conn; } //釋放數(shù)據(jù)庫連接 public static void releaseConnection(Connection conn) { try { if (conn != null) conn.close(); } catch (Exception e) { e.printStackTrace(); } } }
//查詢數(shù)據(jù),結(jié)果倒著顯示,倒序 package javacore1; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class Query { public static void main(String[] args) { Connection conn = getConnection("h5", "111");// 獲取數(shù)據(jù)庫連接 query(conn); //方法名調(diào)用數(shù)據(jù)庫連接 releaseConnection(conn);//釋放數(shù)據(jù)庫連接 } //查詢數(shù)據(jù),定義的query方法 public static void query(Connection conn){ String Sql="select * from employees"; try{ Statement stmt=conn.createStatement(); //也可以使用PreparedStatement來做 ResultSet rs=stmt.executeQuery(Sql);//執(zhí)行sql語句并返還結(jié)束 rs.afterLast(); //先跳到最后一行 while(rs.previous()){//遍歷結(jié)果集 ,向上一行 System.out.println("人員編號:"+rs.getString("employee_id")+"工資:"+rs.getString("salary")); } if(rs !=null){ try{ rs.close(); } catch (SQLException e){ e.printStackTrace(); } } if(stmt !=null){ try{ stmt.close(); }catch(SQLException e){ e.printStackTrace(); } } if(conn !=null){ try{ conn.close(); }catch(SQLException e){ e.printStackTrace(); } } }catch(Exception e){ e.printStackTrace(); } } //數(shù)據(jù)庫連接 public static Connection getConnection(String user, String pass) { Connection conn = null;//聲明連接對象 String driver = "com.mysql.jdbc.Driver";// 驅(qū)動程序類名 String url = "jdbc:mysql://localhost:3306/test?" // 數(shù)據(jù)庫URL + "useUnicode=true&characterEncoding=UTF8";// 防止亂碼 try { Class.forName(driver);// 注冊(加載)驅(qū)動程序 conn = DriverManager.getConnection(url, user, pass);// 獲取數(shù)據(jù)庫連接 } catch (Exception e) { e.printStackTrace(); } return conn; } //釋放數(shù)據(jù)庫連接 public static void releaseConnection(Connection conn) { try { if (conn != null) conn.close(); } catch (Exception e) { e.printStackTrace(); } } }
2.插入數(shù)據(jù)
package javacore1; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.sql.Statement; public class Insert { public static void main(String[] args) { Connection conn = getConnection("h4", "111111"); // 獲取數(shù)據(jù)庫連接 insert(conn); //方法名調(diào)用數(shù)據(jù)庫連接 releaseConnection(conn);// 釋放數(shù)據(jù)庫連接 } //插入數(shù)據(jù) public static void insert(Connection conn) { try { String sql = "insert into employees(employee_id,last_name,salary,department_id,userid)" + " values ('100010', 'xiaogou', '7000','004','9')"; // 插入數(shù)據(jù)的sql語句 Statement stmt1 =conn.createStatement(); // 創(chuàng)建用于執(zhí)行靜態(tài)sql語句的Statement對象 int count = stmt1.executeUpdate(sql); // 執(zhí)行插入操作的sql語句,并返回插入數(shù)據(jù)的個數(shù) System.out.println("向biao中插入了 " + count + " 條數(shù)據(jù)"); //輸出插入操作的處理結(jié)果 conn.close(); //關(guān)閉數(shù)據(jù)庫連接 } catch (SQLException e) { e.printStackTrace(); } } //數(shù)據(jù)庫連接 public static Connection getConnection(String user, String pass) { Connection conn = null;//聲明連接對象 String driver = "com.mysql.jdbc.Driver";// 驅(qū)動程序類名 String url = "jdbc:mysql://localhost:3306/test?" // 數(shù)據(jù)庫URL + "useUnicode=true&characterEncoding=UTF8";// 防止亂碼 try { Class.forName(driver);// 注冊(加載)驅(qū)動程序 conn = DriverManager.getConnection(url, user, pass);// 獲取數(shù)據(jù)庫連接 } catch (Exception e) { e.printStackTrace(); } return conn; } //釋放數(shù)據(jù)庫連接 public static void releaseConnection(Connection conn) { try { if (conn != null) conn.close(); } catch (Exception e) { e.printStackTrace(); } } }
3.刪除數(shù)據(jù)
package javacore1; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.sql.Statement; public class Delete { public static void main(String[] args) { Connection conn = getConnection("h4", "111111"); // 獲取數(shù)據(jù)庫連接 delete(conn); //方法名調(diào)用數(shù)據(jù)庫連接; releaseConnection(conn);// 釋放數(shù)據(jù)庫連接 } //刪除數(shù)據(jù) public static void delete(Connection conn){ String Sql = "delete from employees where employee_id=100009"; try { Statement stmt = conn.createStatement();// 或者用PreparedStatement方法 stmt.executeUpdate(Sql);//執(zhí)行sql語句 if (stmt != null) { try { stmt.close(); } catch (SQLException e) { e.printStackTrace(); } } } catch (SQLException e) { e.printStackTrace(); } } //數(shù)據(jù)庫連接 public static Connection getConnection(String user, String pass) { Connection conn = null;//聲明連接對象 String driver = "com.mysql.jdbc.Driver";// 驅(qū)動程序類名 String url = "jdbc:mysql://localhost:3306/test?" // 數(shù)據(jù)庫URL + "useUnicode=true&characterEncoding=UTF8";// 防止亂碼 try { Class.forName(driver);// 注冊(加載)驅(qū)動程序 conn = DriverManager.getConnection(url, user, pass);// 獲取數(shù)據(jù)庫連接 } catch (Exception e) { e.printStackTrace(); } return conn; } // 釋放數(shù)據(jù)庫連接 public static void releaseConnection(Connection conn) { try { if (conn != null) conn.close(); } catch (Exception e) { e.printStackTrace(); } } }
4.更新數(shù)據(jù)
package javacore1; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.sql.Statement; public class Update { public static void main(String[] args) { Connection conn = getConnection("h4", "111111"); // 獲取數(shù)據(jù)庫連接 update(conn); //方法名調(diào)用數(shù)據(jù)庫連接 releaseConnection(conn);// 釋放數(shù)據(jù)庫連接 } //更新數(shù)據(jù) public static void update(Connection conn){ String Sql = "update employees set salary=8000 where employee_id=100005"; try { Statement stmt1 = conn.createStatement();//或者用PreparedStatement方法 stmt1.executeUpdate(Sql);//執(zhí)行sql語句 if (stmt1 != null) { try { stmt1.close(); } catch (SQLException e) { e.printStackTrace(); } } } catch (SQLException e) { e.printStackTrace(); } } //數(shù)據(jù)庫連接 public static Connection getConnection(String user, String pass) { Connection conn = null;//聲明連接對象 String driver = "com.mysql.jdbc.Driver";// 驅(qū)動程序類名 String url = "jdbc:mysql://localhost:3306/test?" // 數(shù)據(jù)庫URL + "useUnicode=true&characterEncoding=UTF8";// 防止亂碼 try { Class.forName(driver);// 注冊(加載)驅(qū)動程序 conn = DriverManager.getConnection(url, user, pass);// 獲取數(shù)據(jù)庫連接 } catch (Exception e) { e.printStackTrace(); } return conn; } // 釋放數(shù)據(jù)庫連接 public static void releaseConnection(Connection conn) { try { if (conn != null) conn.close(); } catch (Exception e) { e.printStackTrace(); } } }
5.轉(zhuǎn)賬(事物處理)
JDBC處理事務(wù)通過關(guān)閉連接的自動提交實現(xiàn)的:
Connection.setAutoCommit(false);
提交事務(wù):
Connection.commit();
回滾事務(wù)
回滾部分:
Connection.rollback(Savepoint);
全部回滾:
Connection.rollback();
package javacore1; import java.sql.Connection; import java.sql.DriverManager; import java.sql.Statement; public class TransCash { public static void main(String[] args) { Connection conn = getConnection("h4", "111111");// 獲取數(shù)據(jù)庫連接 transCash(conn); //方法名調(diào)用數(shù)據(jù)庫連接 releaseConnection(conn);// 釋放數(shù)據(jù)庫連接 } //轉(zhuǎn)賬(數(shù)據(jù)調(diào)換)(原來數(shù)據(jù)為100和500,實現(xiàn)這個功能后變成500和100,其實說白了就是更新數(shù)據(jù),改數(shù)據(jù)) public static void transCash(Connection conn){ Statement stmt = null; try{ conn.setAutoCommit(false);//關(guān)閉自動提交 String sql = "update employees set salary=500 where employee_id=100001"; stmt = conn.createStatement(); stmt.executeUpdate(sql); sql = "update employees set salary=100 where employee_id=100002"; stmt.executeUpdate(sql);//執(zhí)行sql語句,上面的兩個工資將會調(diào)換 //int i=1/0;如果這樣,那么將不可以完成任務(wù),因為這是一個完整的事物,有一點失敗,將全部失敗 conn.commit();//提交事務(wù) }catch(Exception e){ e.printStackTrace(); }finally{ try{ if(stmt != null){ stmt.close(); }catch(Exception e){ e.printStackTrace(); } } } //數(shù)據(jù)庫連接 public static Connection getConnection(String user, String pass) { Connection conn = null;//聲明連接對象 String driver = "com.mysql.jdbc.Driver";// 驅(qū)動程序類名 String url = "jdbc:mysql://localhost:3306/test?" // 數(shù)據(jù)庫URL + "useUnicode=true&characterEncoding=UTF8";// 防止亂碼 try { Class.forName(driver);// 注冊(加載)驅(qū)動程序 conn = DriverManager.getConnection(url, user, pass);// 獲取數(shù)據(jù)庫連接 } catch (Exception e) { e.printStackTrace(); } return conn; } //釋放數(shù)據(jù)庫連接 public static void releaseConnection(Connection conn) { try { if (conn != null) conn.close(); } catch (Exception e) { e.printStackTrace(); } } }
6.查找存儲過程無參數(shù)的
package javacore1; import java.sql.CallableStatement; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; public class ExecProc { public static void main(String[] args) { Connection conn = getConnection("h4", "111111"); // 獲取數(shù)據(jù)庫連接 execProc(conn);//方法名調(diào)用數(shù)據(jù)庫連接 releaseConnection(conn);// 釋放數(shù)據(jù)庫連接 } //調(diào)用無參存儲過程; public static void execProc(Connection conn){ String sql = "{call raisesalary}"; try { CallableStatement cstmt = conn.prepareCall(sql); cstmt.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); } } //數(shù)據(jù)庫連接 public static Connection getConnection(String user, String pass) { Connection conn = null;//聲明連接對象 String driver = "com.mysql.jdbc.Driver";// 驅(qū)動程序類名 String url = "jdbc:mysql://localhost:3306/test?" // 數(shù)據(jù)庫URL + "useUnicode=true&characterEncoding=UTF8";// 防止亂碼 try { Class.forName(driver);// 注冊(加載)驅(qū)動程序 conn = DriverManager.getConnection(url, user, pass);// 獲取數(shù)據(jù)庫連接 } catch (Exception e) { e.printStackTrace(); } return conn; } // 釋放數(shù)據(jù)庫連接 public static void releaseConnection(Connection conn) { try { if (conn != null) conn.close(); } catch (Exception e) { e.printStackTrace(); } } }
7.查找存儲過程有參數(shù)的
package javacore1; import java.sql.CallableStatement; import java.sql.Connection; import java.sql.DriverManager; import java.sql.Types; public class GetMulti { public static void main(String[] args) { Connection conn = getConnection("h4", "111111"); // 獲取數(shù)據(jù)庫連接 int cnt = getMulti(conn); //查找存儲過程; System.out.println("人員編號:" + cnt); releaseConnection(conn);// 釋放數(shù)據(jù)庫連接 } //查找存儲過程有參 public static int getMulti(Connection conn) { int dept_id = 100001; int cnt = 0; String sql = "{call calc_emp_count(?,?)}"; try { CallableStatement cstmt = conn.prepareCall(sql);//初始化Statement對象 cstmt.setInt(1, dept_id);//CallableStatement.setxxx(參數(shù),值)或者(數(shù)字,值),而PreparedStatement.setxxx(數(shù)字,值)只能這樣 cstmt.setInt(2, cnt); // cstmt.registerOutParameter(2, Types.INTEGER);//聲明輸出參數(shù) cstmt.executeUpdate();//執(zhí)行sql語句 cnt = cstmt.getInt(2);//獲取結(jié)果 if (cstmt != null) { cstmt.close();// 釋放Statement對象 } } catch (Exception e) { e.printStackTrace(); } return cnt; } //數(shù)據(jù)庫連接 public static Connection getConnection(String user, String pass) { Connection conn = null;//聲明連接對象 String driver = "com.mysql.jdbc.Driver";// 驅(qū)動程序類名 String url = "jdbc:mysql://localhost:3306/test?" // 數(shù)據(jù)庫URL + "useUnicode=true&characterEncoding=UTF8";// 防止亂碼 try { Class.forName(driver);// 注冊(加載)驅(qū)動程序 conn = DriverManager.getConnection(url, user, pass);// 獲取數(shù)據(jù)庫連接 } catch (Exception e) { e.printStackTrace(); } return conn; } //釋放數(shù)據(jù)庫連接 public static void releaseConnection(Connection conn) { try { if (conn != null) conn.close(); } catch (Exception e) { e.printStackTrace(); } } }
8.普通處理
package javacore1; import java.sql.Connection; import java.sql.Date; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.SQLException; public class PlanInsert { public static void main(String[] args) { Connection conn = getConnection("h4", "111111"); // 獲取數(shù)據(jù)庫連接 planInsert(conn); //方法名調(diào)用數(shù)據(jù)庫連接 releaseConnection(conn);// 釋放數(shù)據(jù)庫連接 } //普通處理 public static void planInsert(Connection conn) { try { String sql = "insert into test_user1(userid,username,loadingtime)" + " values (?,?,?)"; // 插入數(shù)據(jù)的sql語句 PreparedStatement pstmt=conn.prepareStatement(sql); long startTime=System.currentTimeMillis(); for(int i=0;i<1000;i++){ pstmt.setLong(1, i); pstmt.setString(2, "user"+i); pstmt.setDate(3, new Date(System.currentTimeMillis())); pstmt.executeUpdate(); } System.out.println("總共耗時:"+(System.currentTimeMillis() - startTime)); pstmt.close(); //關(guān)閉數(shù)據(jù)庫連接 } catch (SQLException e) { e.printStackTrace(); } } //數(shù)據(jù)庫連接 public static Connection getConnection(String user, String pass) { Connection conn = null;//聲明連接對象 String driver = "com.mysql.jdbc.Driver";// 驅(qū)動程序類名 String url = "jdbc:mysql://localhost:3306/test?" // 數(shù)據(jù)庫URL + "useUnicode=true&characterEncoding=UTF8";// 防止亂碼 try { Class.forName(driver);// 注冊(加載)驅(qū)動程序 conn = DriverManager.getConnection(url, user, pass);// 獲取數(shù)據(jù)庫連接 } catch (Exception e) { e.printStackTrace(); } return conn; } //釋放數(shù)據(jù)庫連接 public static void releaseConnection(Connection conn) { try { if (conn != null) conn.close(); } catch (Exception e) { e.printStackTrace(); } } }
9.批量處理
獲得原來JDBC事務(wù)的模式:
boolean currentTransactionModle = con.getAutoCommit();
設(shè)置成事務(wù)模式(關(guān)閉自動提交):
con.setAutoCommit(false);
Statement stm = con.createStatement();
三個異構(gòu)的sql語句:
String sql1 = "delete from user where id = 8";
String sql2 = "update user set name='java' where id = 7";
String sql3 = "insert into user(name,password) values('jdbc','jdbc')";
添加到Statement的批量處理緩沖區(qū)中:
stm.addBatch(sql1);
stm.addBatch(sql2);
stm.addBatch(sql3);
執(zhí)行批量更新:
stm.executeBatch();
提交本次批量更新的事務(wù):
con.commit();
回復(fù)原來的事務(wù)模式:
con.setAutoCommit(currentTransactionModle);
package javacore1; import java.sql.Connection; import java.sql.Date; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.SQLException; public class BatchInsert { public static void main(String[] args) { Connection conn = getConnection("h4", "111111"); // 獲取數(shù)據(jù)庫連接 batchInsert(conn); //方法名調(diào)用數(shù)據(jù)庫連接 releaseConnection(conn);// 釋放數(shù)據(jù)庫連接 } //批量插入的速度要比普通處理的速度快 public static void batchInsert(Connection conn) { try { String sql = "insert into test_user1(userid,username,loadingtime)" + " values (?,?,?)"; // 插入數(shù)據(jù)的sql語句 PreparedStatement pstmt=conn.prepareStatement(sql); long startTime=System.currentTimeMillis(); for(int i=0;i<1000;i++){ pstmt.setLong(1, i); pstmt.setString(2, "user"+i); pstmt.setDate(3, new Date(System.currentTimeMillis())); pstmt.addBatch();//添加到批量處理 } int[] result=pstmt.executeBatch(); System.out.println("總共耗時:"+(System.currentTimeMillis() - startTime)); pstmt.close(); //關(guān)閉數(shù)據(jù)庫連接 } catch (SQLException e) { e.printStackTrace(); } } //數(shù)據(jù)庫連接 public static Connection getConnection(String user, String pass) { Connection conn = null;//聲明連接對象 String driver = "com.mysql.jdbc.Driver";// 驅(qū)動程序類名 String url = "jdbc:mysql://localhost:3306/test?" // 數(shù)據(jù)庫URL + "useUnicode=true&characterEncoding=UTF8";// 防止亂碼 try { Class.forName(driver);// 注冊(加載)驅(qū)動程序 conn = DriverManager.getConnection(url, user, pass);// 獲取數(shù)據(jù)庫連接 } catch (Exception e) { e.printStackTrace(); } return conn; } //釋放數(shù)據(jù)庫連接 public static void releaseConnection(Connection conn) { try { if (conn != null) conn.close(); } catch (Exception e) { e.printStackTrace(); } } }
10.分頁顯示
package javacore1; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; public class Paging { public static void main(String[] args) { Connection conn = getConnection("h4", "111111"); // 獲取數(shù)據(jù)庫連接 paging(conn,1,3);//方法名調(diào)用數(shù)據(jù)庫連接,且定義顯示第幾行到第幾行 releaseConnection(conn);// 釋放數(shù)據(jù)庫連接 } //分頁查詢 public static void paging(Connection conn,int startIndex,int total){ try{ String sql="select * from employees limit ?,?"; PreparedStatement pstmt=conn.prepareStatement(sql); pstmt.setInt(1, startIndex); pstmt.setInt(2, total); ResultSet rs=pstmt.executeQuery(); while(rs.next()){ System.out.print("工號:"+rs.getInt(1)); System.out.println("部門編號:"+rs.getInt("department_id")); }rs.close(); pstmt.close(); }catch(SQLException e){ e.printStackTrace(); } } //數(shù)據(jù)庫連接 public static Connection getConnection(String user, String pass) { Connection conn = null;//聲明連接對象 String driver = "com.mysql.jdbc.Driver";// 驅(qū)動程序類名 String url = "jdbc:mysql://localhost:3306/test?" // 數(shù)據(jù)庫URL + "useUnicode=true&characterEncoding=UTF8";// 防止亂碼 try { Class.forName(driver);// 注冊(加載)驅(qū)動程序 conn = DriverManager.getConnection(url, user, pass);// 獲取數(shù)據(jù)庫連接 } catch (Exception e) { e.printStackTrace(); } return conn; } // 釋放數(shù)據(jù)庫連接 public static void releaseConnection(Connection conn) { try { if (conn != null) conn.close(); } catch (Exception e) { e.printStackTrace(); } } }
11.結(jié)果集可以滾動
package javacore1; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; public class ScrpllResult { public static void main(String[] args) { Connection conn = getConnection("h4", "111111"); // 獲取數(shù)據(jù)庫連接 scrpllResult(conn);//方法名調(diào)用數(shù)據(jù)庫連接 releaseConnection(conn);// 釋放數(shù)據(jù)庫連接 } //結(jié)果集滾動顯示 public static void scrpllResult(Connection conn){ try{ String sql="select * from employees"; //結(jié)果集可以滾動 //并發(fā)性,結(jié)果集只讀,不可以修改 PreparedStatement pstmt=conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY); ResultSet rs=pstmt.executeQuery(); while(rs.next()){ //向下一行遍歷 System.out.print("工號:"+rs.getLong(1)); System.out.println("名字"+rs.getString("last_name")); }while(rs.previous()){//向上一行遍歷 System.out.print("工號:"+rs.getLong(1)); System.out.println("工資"+rs.getInt("salary")); } rs.absolute(6);//表示直接跳到第幾行 if(rs.next()){ System.out.print("工號:"+rs.getLong(1)); System.out.println("..........部門編號:"+rs.getString("department_id")); } rs.close(); pstmt.close(); }catch(SQLException e){ e.printStackTrace(); } } //數(shù)據(jù)庫連接 public static Connection getConnection(String user, String pass) { Connection conn = null;//聲明連接對象 String driver = "com.mysql.jdbc.Driver";// 驅(qū)動程序類名 String url = "jdbc:mysql://localhost:3306/test?" // 數(shù)據(jù)庫URL + "useUnicode=true&characterEncoding=UTF8";// 防止亂碼 try { Class.forName(driver);// 注冊(加載)驅(qū)動程序 conn = DriverManager.getConnection(url, user, pass);// 獲取數(shù)據(jù)庫連接 } catch (Exception e) { e.printStackTrace(); } return conn; } // 釋放數(shù)據(jù)庫連接 public static void releaseConnection(Connection conn) { try { if (conn != null) conn.close(); } catch (Exception e) { e.printStackTrace(); } } }
五.把上面十一個放在一起當(dāng)然最方便省事。(需要用誰,只需把前面的注釋打開便行)
package javacore1; import java.sql.CallableStatement; import java.sql.Connection; import java.sql.Date; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.sql.Types; public class jdbcMySql { public static void main(String[] args) { Connection conn = getConnection("h4", "111111"); // 獲取數(shù)據(jù)庫連接 //query(conn); //1.查詢數(shù)據(jù) //insert(conn);//2.插入數(shù)據(jù) //delete(conn);//3.刪除數(shù)據(jù) //update(conn);//4.更新數(shù)據(jù) //transCash(conn);//5.轉(zhuǎn)賬 //execProc(conn);//6.查找存儲過程無參數(shù)的 //int cnt = getMulti(conn);//7.查找存儲過程有參數(shù)的 //System.out.println("人員編號:" + cnt);//查找存儲過程有參數(shù)的 //planInsert(conn);//8.普通處理 //batchInsert(conn);//9.批量處理 // paging(conn,1,3);//10.分頁顯示 //scrpllResult(conn);//11.結(jié)果集可以滾動 releaseConnection(conn);// 釋放數(shù)據(jù)庫連接 } //1.查詢數(shù)據(jù),定義的query方法 public static void query(Connection conn){ String Sql="select * from employees"; try{ Statement stmt=conn.createStatement(); //也可以使用PreparedStatement來做 ResultSet rs=stmt.executeQuery(Sql);//執(zhí)行sql語句并返還結(jié)束 while(rs.next()){//遍歷結(jié)果集 System.out.println("人員編號:"+rs.getString("employee_id")+"工資:"+rs.getString("salary")); } if(rs !=null){ try{ rs.close(); } catch (SQLException e){ e.printStackTrace(); } } if(stmt !=null){ try{ stmt.close(); }catch(SQLException e){ e.printStackTrace(); } } if(conn !=null){ try{ conn.close(); }catch(SQLException e){ e.printStackTrace(); } } }catch(Exception e){ e.printStackTrace(); } } //2.插入數(shù)據(jù) public static void insert(Connection conn) { try { String sql = "insert into employees(employee_id,last_name,salary,department_id,userid)" + " values ('100010', 'xiaogou', '7000','004','9')"; // 插入數(shù)據(jù)的sql語句 Statement stmt1 =conn.createStatement(); // 創(chuàng)建用于執(zhí)行靜態(tài)sql語句的Statement對象 int count = stmt1.executeUpdate(sql); // 執(zhí)行插入操作的sql語句,并返回插入數(shù)據(jù)的個數(shù) System.out.println("向biao中插入了 " + count + " 條數(shù)據(jù)"); //輸出插入操作的處理結(jié)果 conn.close(); //關(guān)閉數(shù)據(jù)庫連接 } catch (SQLException e) { e.printStackTrace(); } } //3.刪除數(shù)據(jù) public static void delete(Connection conn){ String Sql = "delete from employees where employee_id=100009"; try { Statement stmt = conn.createStatement();// 或者用PreparedStatement方法 stmt.executeUpdate(Sql);//執(zhí)行sql語句 if (stmt != null) { try { stmt.close(); } catch (SQLException e) { e.printStackTrace(); } } } catch (SQLException e) { e.printStackTrace(); } } //4.更新數(shù)據(jù) public static void update(Connection conn){ String Sql = "update employees set salary=8000 where employee_id=100005"; try { Statement stmt1 = conn.createStatement();//或者用PreparedStatement方法 stmt1.executeUpdate(Sql);//執(zhí)行sql語句 if (stmt1 != null) { try { stmt1.close(); } catch (SQLException e) { e.printStackTrace(); } } } catch (SQLException e) { e.printStackTrace(); } } //5.轉(zhuǎn)賬(數(shù)據(jù)調(diào)換)(原來數(shù)據(jù)為100和500,實現(xiàn)這個功能后變成500和100,其實說白了就是更新數(shù)據(jù),改數(shù)據(jù)) public static void transCash(Connection conn){ Statement stmt = null; try{ conn.setAutoCommit(false);//關(guān)閉自動提交 String sql = "update employees set salary=500 where employee_id=100001"; stmt = conn.createStatement(); stmt.executeUpdate(sql); sql = "update employees set salary=100 where employee_id=100002"; stmt.executeUpdate(sql);//執(zhí)行sql語句,上面的兩個工資將會調(diào)換 conn.commit();//提交事務(wù) }catch(Exception e){ e.printStackTrace(); }finally{ try{ if(stmt != null)stmt.close(); }catch(Exception e){ e.printStackTrace(); } } } //6.調(diào)用無參存儲過程; public static void execProc(Connection conn){ String sql = "{call raisesalary}"; try { CallableStatement cstmt = conn.prepareCall(sql); cstmt.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); } } //7.查找存儲過程有參 public static int getMulti(Connection conn) { int dept_id = 100001; int cnt = 0; String sql = "{call calc_emp_count(?,?)}"; try { CallableStatement cstmt = conn.prepareCall(sql);//初始化Statement對象 cstmt.setInt(1, dept_id);//CallableStatement.setxxx(參數(shù),值)或者(數(shù)字,值),而PreparedStatement.setxxx(數(shù)字,值)只能這樣 cstmt.setInt(2, cnt); // cstmt.registerOutParameter(2, Types.INTEGER);//聲明輸出參數(shù) cstmt.executeUpdate();//執(zhí)行sql語句 cnt = cstmt.getInt(2);//獲取結(jié)果 if (cstmt != null) { cstmt.close();// 釋放Statement對象 } } catch (Exception e) { e.printStackTrace(); } return cnt; } //8.普通處理 public static void planInsert(Connection conn) { try { String sql = "insert into test_user1(userid,username,loadingtime)" + " values (?,?,?)"; // 插入數(shù)據(jù)的sql語句 PreparedStatement pstmt=conn.prepareStatement(sql); long startTime=System.currentTimeMillis(); for(int i=0;i<1000;i++){ pstmt.setLong(1, i); pstmt.setString(2, "user"+i); pstmt.setDate(3, new Date(System.currentTimeMillis())); pstmt.executeUpdate(); } System.out.println("總共耗時:"+(System.currentTimeMillis() - startTime)); pstmt.close(); //關(guān)閉數(shù)據(jù)庫連接 } catch (SQLException e) { e.printStackTrace(); } } //9.批量插入的速度要比普通處理的速度快 public static void batchInsert(Connection conn) { try { String sql = "insert into test_user1(userid,username,loadingtime)" + " values (?,?,?)"; // 插入數(shù)據(jù)的sql語句 PreparedStatement pstmt=conn.prepareStatement(sql); long startTime=System.currentTimeMillis(); for(int i=0;i<1000;i++){ pstmt.setLong(1, i); pstmt.setString(2, "user"+i); pstmt.setDate(3, new Date(System.currentTimeMillis())); pstmt.addBatch();//添加到批量處理 } int[] result=pstmt.executeBatch(); System.out.println("總共耗時:"+(System.currentTimeMillis() - startTime)); pstmt.close(); //關(guān)閉數(shù)據(jù)庫連接 } catch (SQLException e) { e.printStackTrace(); } } //10.分頁查詢 public static void paging(Connection conn,int startIndex,int total){ try{ String sql="select * from employees limit ?,?"; PreparedStatement pstmt=conn.prepareStatement(sql); pstmt.setInt(1, startIndex); pstmt.setInt(2, total); ResultSet rs=pstmt.executeQuery(); while(rs.next()){ System.out.print("工號:"+rs.getInt(1)); System.out.println("部門編號:"+rs.getInt("department_id")); }rs.close(); pstmt.close(); }catch(SQLException e){ e.printStackTrace(); } } //11.結(jié)果集滾動顯示 public static void scrpllResult(Connection conn){ try{ String sql="select * from employees"; //結(jié)果集可以滾動 //并發(fā)性,結(jié)果集只讀,不可以修改 PreparedStatement pstmt=conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY); ResultSet rs=pstmt.executeQuery(); while(rs.next()){ //向下一行遍歷 System.out.print("工號:"+rs.getLong(1)); System.out.println("名字"+rs.getString("last_name")); }while(rs.previous()){//向上一行遍歷 System.out.print("工號:"+rs.getLong(1)); System.out.println("工資"+rs.getInt("salary")); } rs.absolute(6);//表示直接跳到第幾行 if(rs.next()){ System.out.print("工號:"+rs.getLong(1)); System.out.println("..........部門編號:"+rs.getString("department_id")); } rs.close(); pstmt.close(); }catch(SQLException e){ e.printStackTrace(); } } //數(shù)據(jù)庫連接 public static Connection getConnection(String user, String pass) { Connection conn = null;//聲明連接對象 String driver = "com.mysql.jdbc.Driver";// 驅(qū)動程序類名 String url = "jdbc:mysql://localhost:3306/test?" // 數(shù)據(jù)庫URL + "useUnicode=true&characterEncoding=UTF8";// 防止亂碼 try { Class.forName(driver);// 注冊(加載)驅(qū)動程序 conn = DriverManager.getConnection(url, user, pass);// 獲取數(shù)據(jù)庫連接 } catch (Exception e) { e.printStackTrace(); } return conn; } //釋放數(shù)據(jù)庫連接 public static void releaseConnection(Connection conn) { try { if (conn != null) conn.close(); } catch (Exception e) { e.printStackTrace(); } } }
六.安裝下載的數(shù)據(jù)庫驅(qū)動程序jar包,不同的數(shù)據(jù)庫需要不同的驅(qū)動程序(但是安裝方法都是一樣的)
在使用JDBC編程時需要連接數(shù)據(jù)庫,導(dǎo)入JAR包是必須的,導(dǎo)入其它的jar包方法同樣如此,導(dǎo)入的方法是
打開eclipse
1.右擊要導(dǎo)入jar包的項目,點properties
2.左邊選擇java build path,右邊選擇libraries
3.選擇add External jars
4.選擇jar包的按照路徑下的
確定后就行了。
Java連接MySQL的最新驅(qū)動包下載地址
http://www.mysql.com/downloads/connector/j
1.鼠標(biāo)放在你建的根目錄上面。右擊,然后選擇最下面的properties。
2.然后左邊選擇java build path,右邊選擇libraries ,在選擇右邊的add External jars ,選擇jar包的路徑,點擊確定就可以了
3.裝好后,圖如下出現(xiàn)你要添加的包。
上述就是小編為大家分享的使用JDBC怎么連接MySql數(shù)據(jù)庫并實現(xiàn)查詢、插入、刪除、更新等操作了,如果剛好有類似的疑惑,不妨參照上述分析進(jìn)行理解。如果想知道更多相關(guān)知識,歡迎關(guān)注億速云行業(yè)資訊頻道。
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點不代表本網(wǎng)站立場,如果涉及侵權(quán)請聯(lián)系站長郵箱:is@yisu.com進(jìn)行舉報,并提供相關(guān)證據(jù),一經(jīng)查實,將立刻刪除涉嫌侵權(quán)內(nèi)容。