目錄
- 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 ();
}
}
}
本文來自部落格園，作者：chn-tiancx，轉載請注明原文連結：https://www.cnblogs.com/tiancx/p/15369649.html
![python爬蟲[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)