JDBC

目錄

• ​JDBC​

• ​​JDBC通路資料庫步驟​​
• ​​模拟登入​​
• ​​事務​​

JDBC

JDBC通路資料庫步驟

• 1：加載一個Driver驅動

• 2：建立資料庫連接配接（Connection）

• 3 ：建立SQL指令發送器Statement

• 4：通過Statement發送SQL指令并得到結果

• 5：處理結果（select語句）

• 6：關閉資料庫資源

• ResultSet

• Statement

• Connection

public class TestInsert {
public static void main(String[] args) throws Exception{
String driver = "com.mysql.cj.jdbc.Driver";
String url="jdbc:mysql://127.0.0.1:3306/shiyan?useSSL=false&useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai";
String user="root";
String pasaword="tcx119";
//加載驅動
Class.forName (driver);
//和資料庫建立連接配接
Connection conn = DriverManager.getConnection (url,user,pasaword);
// System.out.println (conn);
//建立sql指令發射器
Statement smt = conn.createStatement ();
//準備sql指令
String sql = "insert into score values(12345666,'X6666',90.2,85.3)";
//處理結果
int n = smt.executeUpdate (sql);
if(n>0){
System.out.println ("插入成功");
        }else{
System.out.println ("插入失敗");
        }
//關閉資源
smt.close ();
conn.close ();
    }
}
      

​

​查詢的結果​

​code​

//這是Emp的代碼
public class Emp {
private String teacherno;
private String tname;
private String major;
private String prof;
private String department;

public Emp() {
    }

public Emp(String teacherno, String tname, String major, String prof, String department) {
this.teacherno = teacherno;
this.tname = tname;
this.major = major;
this.prof = prof;
this.department = department;
    }

public String getTeacherno() {
return teacherno;
    }

public void setTeacherno(String teacherno) {
this.teacherno = teacherno;
    }

public String getTname() {
return tname;
    }

public void setTname(String tname) {
this.tname = tname;
    }

public String getMajor() {
return major;
    }

public void setMajor(String major) {
this.major = major;
    }

public String getProf() {
return prof;
    }

public void setProf(String prof) {
this.prof = prof;
    }

public String getDepartment() {
return department;
    }

public void setDepartment(String department) {
this.department = department;
    }

@Override
public String toString() {
return "Emp{" +
"teacherno='" + teacherno + '\'' +
", tname='" + tname + '\'' +
", major='" + major + '\'' +
", prof='" + prof + '\'' +
", department='" + department + '\'' +
'}';
    }
}
      

//這才是主場
public class TestSelect {
public static void main(String[] args) throws SQLException {
List<Emp> list = selectAll ();
for (Emp emp:list){
System.out.println (emp.getTeacherno ()+"\t"+emp.getTname ()+
"\t"+emp.getMajor ()+"\t"+emp.getProf ()+"\t"+emp.getDepartment ());
        }
    }
//背景處理
public static List<Emp> selectAll() throws SQLException {
String driver = "com.mysql.cj.jdbc.Driver";
String url="jdbc:mysql://127.0.0.1:3306/shiyan?useSSL=false&useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai";
String user="root";
String password="tcx119";
Connection conn=null;
Statement stmt = null;
ResultSet rs=null;
Emp emp = null;
List<Emp> list =new ArrayList<Emp>();
try{
Class.forName (driver);
conn = DriverManager.getConnection (url,user,password);
stmt= conn.createStatement ();
String sql = " select * from teacher";
rs= stmt.executeQuery (sql);
while(rs.next ()){
String teacherno = rs.getString ("teacherno");
String tname = rs.getString ("tname");
String major = rs.getString ("major");
String prof = rs.getString ("prof");
String department = rs.getString ("department");
emp=new Emp (teacherno,tname,major,prof,department);
list.add (emp);
            }
        } catch (ClassNotFoundException | SQLException e) {
e.printStackTrace ();
        } finally {
assert rs != null;
rs.close ();
stmt.close ();
conn.close ();
        }
return list;
    }
}
      

模拟登入

public class Login {
public static void main(String[] args) throws SQLException, ClassNotFoundException {
//前台
Scanner sc = new Scanner (System.in);
System.out.println ("請輸入使用者名");
String userId = sc.next ();
System.out.println ("請輸入密碼");
String inputPassword = sc.next ();
User user = testLogin (userId,inputPassword);
if (user==null){
System.out.println ("登入失敗,請檢查使用者名或密碼是否正确");
        }else{
System.out.println ("登入成功： 姓名： "+user.getRealName ());
        }

    }
//    背景
public static User testLogin(String userId,String inputPassword) throws ClassNotFoundException, SQLException {
String driver = "com.mysql.cj.jdbc.Driver";
String url="jdbc:mysql://127.0.0.1:3306/shiyan?useSSL=false&useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai";
String user="root";
String pasaword="tcx119";
Class.forName (driver);
User user2=null;
Connection conn = DriverManager.getConnection (url,user,pasaword);
String sql = "select * from t_user where userid = ? and password = ? ";

//        Statement stmt = conn.createStatement ();
PreparedStatement pstmt = conn.prepareStatement (sql);
pstmt.setString (1,userId);
pstmt.setString (2,inputPassword);
ResultSet rs =  pstmt.executeQuery ();
if(rs.next ()){
String realName=rs.getString ("realname");
double money = rs.getDouble ("money");
user2 = new User (userId,realName,inputPassword,money);
return user2;
        }
return user2;
    }
}
      

事務

public class TestTransation {
public static void main(String[] args)  {
Connection conn=null;
try{
String driver = "com.mysql.cj.jdbc.Driver";
String url="jdbc:mysql://127.0.0.1:3306/shiyan?useSSL=false&useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai";
String user="root";
String pasaword="tcx119";
Class.forName (driver);
conn = DriverManager.getConnection (url,user,pasaword);
Statement stmt = conn.createStatement ();
conn.setAutoCommit (false);
stmt.executeUpdate ("update t_user set money = money-2000 where userid = 'lisi' ");
stmt.executeUpdate ("update t_user set money = money+2000 where userid = 'zhangsan' ");
conn.commit ();
        } catch (ClassNotFoundException e) {
e.printStackTrace ();
        } catch (SQLException e) {
//手動的復原事務,回到所有DML操作執行之前的狀态
try {
assert conn != null;
conn.rollback();
            } catch (SQLException e1) {
e1.printStackTrace();
            }

e.printStackTrace ();
        }

    }
}