恢復內容開始 1.用JDBC設置aa的balance值為1500 2.用JDBC添加姓名cc,balance為3000 3.用JDBC刪除id為3的數據 4.用JDBC創建一個student表 5.用JDBC查詢account表中所有數據 6.用JDBC查詢account表中所有數據 7.JDBC工 ...
---恢復內容開始---
create table `account` ( `id` int (11), `name` char (60), `balance` int (11) ); insert into `account` (`id`, `name`, `balance`) values('1','aa','2000'); insert into `account` (`id`, `name`, `balance`) values('2','bb','2000'); insert into `account` (`id`, `name`, `balance`) values('3','王五','2000');
1.用JDBC設置aa的balance值為1500
public class JdbcDemo1 { public static void main(String[] args) throws Exception { //1. 導入驅動jar包 //2.註冊驅動 Class.forName("com.mysql.jdbc.Driver"); //3.獲取資料庫連接對象 Connection conn = DriverManager.getConnection("jdbc:mysql:///db3", "root", "root"); //4.定義sql語句 String sql = "update account set balance = 1500 where id = 1"; //5.獲取執行sql的對象 Statement Statement stmt = conn.createStatement(); //6.執行sql int count = stmt.executeUpdate(sql); //7.處理結果 System.out.println(count); //8.釋放資源 stmt.close(); conn.close(); } }
2.用JDBC添加姓名cc,balance為3000
public class JDBCDemo2 { public static void main(String[] args) { Statement stmt = null; Connection conn = null; try { //1. 註冊驅動 Class.forName("com.mysql.jdbc.Driver"); //2. 定義sql String sql = "insert into account values(null,'cc',3000)"; //3.獲取Connection對象 conn = DriverManager.getConnection("jdbc:mysql:///db3", "root", "root"); //4.獲取執行sql的對象 Statement stmt = conn.createStatement(); //5.執行sql int count = stmt.executeUpdate(sql);//影響的行數 //6.處理結果 System.out.println(count); if(count > 0){ System.out.println("添加成功！"); }else{ System.out.println("添加失敗！"); } } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); }finally { //stmt.close(); //7. 釋放資源 //避免空指針異常 if(stmt != null){ try { stmt.close(); } catch (SQLException e) { e.printStackTrace(); } } if(conn != null){ try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } } }
3.用JDBC刪除id為3的數據
public class JDBCDemo3 { public static void main(String[] args) { Connection conn = null; Statement stmt = null; try { //1. 註冊驅動 Class.forName("com.mysql.jdbc.Driver"); //2.獲取連接對象 conn = DriverManager.getConnection("jdbc:mysql:///db3", "root", "root"); //conn = JDBCUtils.getConnection("jdbc:mysql:///db3", "root", "root"); //3.定義sql String sql = "delete from account where id = 3"; //4.獲取執行sql對象 stmt = conn.createStatement(); //5.執行sql int count = stmt.executeUpdate(sql); //6.處理結果 System.out.println(count); if(count > 0){ System.out.println("刪除成功！"); }else{ System.out.println("刪除失敗"); } } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } finally { //7.釋放資源 if(stmt != null){ try { stmt.close(); } catch (SQLException e) { e.printStackTrace(); } } if(conn != null){ try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } } }
4.用JDBC創建一個student表
/** * 執行DDL語句 */ public class JDBCDemo5 { public static void main(String[] args) { Connection conn = null; Statement stmt = null; try { //1. 註冊驅動 Class.forName("com.mysql.jdbc.Driver"); //2.獲取連接對象 conn = DriverManager.getConnection("jdbc:mysql:///db3", "root", "root"); //3.定義sql String sql = "create table student1 (id int , name varchar(20))"; //4.獲取執行sql對象 stmt = conn.createStatement(); //5.執行sql int count = stmt.executeUpdate(sql); //6.處理結果 System.out.println(count); } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } finally { //7.釋放資源 if(stmt != null){ try { stmt.close(); } catch (SQLException e) { e.printStackTrace(); } } if(conn != null){ try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } } }
5.用JDBC查詢account表中所有數據
/** * 執行DDL語句 */ public class JDBCDemo7 { public static void main(String[] args) { Connection conn = null; Statement stmt = null; ResultSet rs = null; try { //1. 註冊驅動 Class.forName("com.mysql.jdbc.Driver"); //2.獲取連接對象 conn = DriverManager.getConnection("jdbc:mysql:///db3", "root", "root"); //3.定義sql String sql = "select * from account"; //4.獲取執行sql對象 stmt = conn.createStatement(); //5.執行sql rs = stmt.executeQuery(sql); //6.處理結果 //迴圈判斷游標是否是最後一行末尾。 while(rs.next()){ //獲取數據 //6.2 獲取數據 int id = rs.getInt(1); String name = rs.getString("name"); double balance = rs.getDouble(3); System.out.println(id + "---" + name + "---" + balance); } /* //6.1 讓游標向下移動一行 if(rs.next()){ //判斷是否有數據 //6.2 獲取數據 int id = rs.getInt(1); String name = rs.getString("name"); double balance = rs.getDouble(3); System.out.println(id + "---" + name + "---" + balance); } //6.1 讓游標向下移動一行 if(rs.next()){ //判斷是否有數據 //6.2 獲取數據 int id = rs.getInt(1); String name = rs.getString("name"); double balance = rs.getDouble(3); System.out.println(id + "---" + name + "---" + balance); } //6.1 讓游標向下移動一行 if(rs.next()){ //判斷是否有數據 //6.2 獲取數據 int id = rs.getInt(1); String name = rs.getString("name"); double balance = rs.getDouble(3); System.out.println(id + "---" + name + "---" + balance); } //6.1 讓游標向下移動一行 if(rs.next()){ //判斷是否有數據 //6.2 獲取數據 int id = rs.getInt(1); String name = rs.getString("name"); double balance = rs.getDouble(3); System.out.println(id + "---" + name + "---" + balance); }*/ /* //6.1 讓游標向下移動一行 rs.next(); //6.2 獲取數據 int id2 = rs.getInt(1); String name2 = rs.getString("name"); double balance2 = rs.getDouble(3); System.out.println(id2 + "---" + name2 + "---" + balance2); //6.1 讓游標向下移動一行 rs.next(); //6.2 獲取數據 int id3 = rs.getInt(1); String name3 = rs.getString("name"); double balance3 = rs.getDouble(3); System.out.println(id3 + "---" + name3 + "---" + balance3);*/ } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } finally { //7.釋放資源 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(); } } } } }
6.用JDBC查詢account表中所有數據
public class Emp { private int id; private String ename; private int job_id; private int mgr; private Date joindate; private double salary; private double bonus; private int dept_id; public int getId() { return id; } public void setId(int id) { this.id = id; } public String getEname() { return ename; } public void setEname(String ename) { this.ename = ename; } public int getJob_id() { return job_id; } public void setJob_id(int job_id) { this.job_id = job_id; } public int getMgr() { return mgr; } public void setMgr(int mgr) { this.mgr = mgr; } public Date getJoindate() { return joindate; } public void setJoindate(Date joindate) { this.joindate = joindate; } public double getSalary() { return salary; } public void setSalary(double salary) { this.salary = salary; } public int getDept_id() { return dept_id; } public void setDept_id(int dept_id) { this.dept_id = dept_id; } public double getBonus() { return bonus; } public void setBonus(double bonus) { this.bonus = bonus; } @Override public String toString() { return "Emp{" + "id=" + id + ", ename='" + ename + '\'' + ", job_id=" + job_id + ", mgr=" + mgr + ", joindate=" + joindate + ", salary=" + salary + ", bonus=" + bonus + ", dept_id=" + dept_id + '}'; } }
public class JDBCDemo8 { public static void main(String[] args) { List<Emp> list = new JDBCDemo8().findAll(); System.out.println(list); System.out.println(list.size()); } /** * 查詢所有emp對象 * @return */ public List<Emp> findAll(){ Connection conn = null; Statement stmt = null; ResultSet rs = null; List<Emp> list = null; try { //1.註冊驅動 Class.forName("com.mysql.jdbc.Driver"); //2.獲取連接 conn = DriverManager.getConnection("jdbc:mysql:///db3", "root", "root"); //3.定義sql String sql = "select * from emp"; //4.獲取執行sql的對象 stmt = conn.createStatement(); //5.執行sql rs = stmt.executeQuery(sql); //6.遍歷結果集，封裝對象，裝載集合 Emp emp = null; list = new ArrayList<Emp>(); while(rs.next()){ //獲取數據 int id = rs.getInt("id"); String ename = rs.getString("name"); String ename = rs.getString(""); Date joindate = rs.getDate("joindate"); double salary = rs.getDouble("salary"); int dept_id = rs.getInt("dept_id"); // 創建emp對象,並賦值 emp = new Emp(); emp.setId(id); emp.setEname(ename); emp.setJob_id(job_id); emp.setMgr(mgr); emp.setJoindate(joindate); emp.setSalary(salary); emp.setBonus(bonus); emp.setDept_id(dept_id); //裝載集合 list.add(emp); } } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); }finally { 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(); } } } return list; } /** * 演示JDBC工具類 * @return */ public List<Emp> findAll2(){ Connection conn = null; Statement stmt = null; ResultSet rs = null; List<Emp> list = null; try { /* //1.註冊驅動 Class.forName("com.mysql.jdbc.Driver"); //2.獲取連接 conn = DriverManager.getConnection("jdbc:mysql:///db3", "root", "root");*/ conn = JDBCUtils.getConnection(); //3.定義sql String sql = "select * from emp"; //4.獲取執行sql的對象 stmt = conn.createStatement(); //5.執行sql rs = stmt.executeQuery(sql); //6.遍歷結果集，封裝對象，裝載集合 Emp emp = null; list = new ArrayList<Emp>(); while(rs.next()){ //獲取數據 int id = rs.getInt("id"); String ename = rs.getString("ename"); int job_id = rs.getInt("job_id"); int mgr = rs.getInt("mgr"); Date joindate = rs.getDate("joindate"); double salary = rs.getDouble("salary"); double bonus = rs.getDouble("bonus"); int dept_id = rs.getInt("dept_id"); // 創建emp對象,並賦值 emp = new Emp(); emp.setId(id); emp.setEname(ename); emp.setJob_id(job_id); emp.setMgr(mgr); emp.setJoindate(joindate); emp.setSalary(salary); emp.setBonus(bonus); emp.setDept_id(dept_id); //裝載集合 list.add(emp); } } catch (SQLException e) { e.printStackTrace(); }finally { /*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(); } }*/ JDBCUtils.close(rs,stmt,conn); } return list; } }
7.JDBC工具類
/** * JDBC工具類 */ public class JDBCUtils { private static String url; private static String user; private static String password; private static String driver; /** * 文件的讀取，只需要讀取一次即可拿到這些值。使用靜態代碼塊 */ static{ //讀取資源文件，獲取值。 try { //1. 創建Properties集合類。 Properties pro = new Properties(); //獲取src路徑下的文件的方式--->ClassLoader 類載入器 ClassLoader classLoader = JDBCUtils.class.getClassLoader(); URL res = classLoader.getResource("jdbc.properties"); String path = res.getPath(); // System.out.println(path);///D:/IdeaProjects/itcast/out/production/day04_jdbc/jdbc.properties //2. 載入文件 // pro.load(new FileReader("D:\\IdeaProjects\\itcast\\day04_jdbc\\src\\jdbc.properties")); pro.load(new FileReader(path)); //3. 獲取數據，賦值 url = pro.getProperty("url"); user = pro.getProperty("user"); password = pro.getProperty("password"); driver = pro.getProperty("driver"); //4. 註冊驅動 Class.forName(driver); } catch (IOException e) { e.printStackTrace(); } catch (ClassNotFoundException e) { e.printStackTrace(); } } /** * 獲取連接 * @return 連接對象 */ public static Connection getConnection() throws SQLException { return DriverManager.getConnection(url, user, password); } /** * 釋放資源 * @param stmt * @param conn */ public static void close(Statement stmt,Connection conn){ if( stmt != null){ try { stmt.close(); } catch (SQLException e) { e.printStackTrace(); } } if( conn != null){ try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } /** * 釋放資源 * @param stmt * @param conn */ public static void close(ResultSet rs,Statement stmt, Connection conn){ 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(); } } } }
url=jdbc:mysql:mysql:///db3 user=root password=root driver=com.mysql.jdbc.Driver
8.通過鍵盤錄入用戶名和密碼，判斷用戶是否登錄成功
/** * 練習： * * 需求： * 1. 通過鍵盤錄入用戶名和密碼 * 2. 判斷用戶是否登錄成功 */ public class JDBCDemo9 { public static void main(String[] args) { //1.鍵盤錄入，接受用戶名和密碼 Scanner sc = new Scanner(System.in); System.out.println("請輸入用戶名："); String username = sc.nextLine(); System.out.println("請輸入密碼："); String password = sc.nextLine(); //2.調用方法 boolean flag = new JDBCDemo9().login2(username, password); //3.判斷結果，輸出不同語句 if(flag){ //登錄成功 System.out.println("登錄成功！"); }else{ System.out.println("用戶名或密碼錯誤！"); } } /** * 登錄方法 */ public boolean login(String username ,String password){ if(username == null || password == null){ return false; } //連接資料庫判斷是否登錄成功 Connection conn = null; Statement stmt = null; ResultSet rs = null; //1.獲取連接 try { conn = JDBCUtils.getConnection(); //2.定義sql String sql = "select * from user where username = '"+username+"' and password = '"+password+"' "; System.out.println(sql); //3.獲取執行sql的對象 stmt = conn.createStatement(); //4.執行查詢 rs = stmt.executeQuery(sql); //5.判斷 /* if(rs.next()){//如果有下一行，則返回true return true; }else{ return false; }*/ return rs.next();//如果有下一行，則返回true } catch (SQLException e) { e.printStackTrace(); }finally { JDBCUtils.close(rs,stmt,conn); } return false; } /** * 登錄方法,使用PreparedStatement實現 */ public boolean login2(String username ,String password){ if(username == null || password == null){ return false; } //連接資料庫判斷是否登錄成功 Connection conn = null; PreparedStatement pstmt = null; ResultSet rs = null; //1.獲取連接 try { conn = JDBCUtils.getConnection(); //2.定義sql String sql = "select * from user where username = ? and password = ?"; //3.獲取執行sql的對象 pstmt = conn.prepareStatement(sql); //給?賦值 pstmt.setString(1,username); pstmt.setString(2,password); //4.執行查詢,不需要傳遞sql rs = pstmt.executeQuery(); //5.判斷 /* if(rs.next()){//如果有下一行，則返回true return true; }else{ return false; }*/ return rs.next();//如果有下一行，則返回true } catch (SQLException e) { e.printStackTrace(); }finally { JDBCUtils.close(rs,pstmt,conn); } return false; } }
9.JDBC事務操作
/** * 事務操作 */ public class JDBCDemo10 { public static void main(String[] args) { Connection conn = null; PreparedStatement pstmt1 = null; PreparedStatement pstmt2 = null; try { //1.獲取連接 conn = JDBCUtils.getConnection(); //開啟事務 conn.setAutoCommit(false); //2.定義sql //2.1 張三 - 500 String sql1 = "update account set balance = balance - ? where id = ?"; //2.2 李四 + 500 String sql2 = "update account set balance = balance + ? where id = ?"; //3.獲取執行sql對象 pstmt1 = conn.prepareStatement(sql1); pstmt2 = conn.prepareStatement(sql2); //4. 設置參數 pstmt1.setDouble(1,500); pstmt1.setInt(2,1); pstmt2.setDouble(1,500); pstmt2.setInt(2,2); //5.執行sql pstmt1.executeUpdate(); // 手動製造異常 int i = 3/0; pstmt2.executeUpdate(); //提交事務 conn.commit(); } catch (Exception e) { //事務回滾 try { if(conn != null) { conn.rollback(); } } catch (SQLException e1) { e1.printStackTrace(); } e.printStackTrace(); }finally { JDBCUtils.close(pstmt1,conn); JDBCUtils.close(pstmt2,null); } } }
---恢復內容結束---
