package com.neuedu.jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import org.junit.Test;
public class Insert {
// 查詢 select select * from 表名 where 字段名=''
@Test
public void select() throws ClassNotFoundException, SQLException {
/*
* 創(chuàng)建jdbc應用步驟分7步 1）載入jdbc驅(qū)動(指定我要連接到哪種數(shù)據(jù)庫，連接不同數(shù)據(jù)庫用不同驅(qū)動)
*
* 2）定義連接url(連接準備 url,端口，用戶名，密碼) 3）建立連接 4）創(chuàng)建PreparedStatement（動態(tài)）
* Statement(靜態(tài)的)(拼sql語句) 5）執(zhí)行數(shù)據(jù)庫命令（crud) 6）結(jié)果的處理 7）關閉連接
*/
// com.mysql.jdbc.Driver driver = new com.mysql.jdbc.Driver();
// 1)載入jdbc驅(qū)動(指定我要連接到哪種數(shù)據(jù)庫，連接不同數(shù)據(jù)庫用不同驅(qū)動)
Class.forName("com.mysql.jdbc.Driver");// (Drive:驅(qū)動)
// 2)定義連接url(連接準備 url,端口，用戶名，密碼)
String url = "jdbc:mysql://localhost:3306/java16?characterEncoding=UTF-8&useUnicode=true";//數(shù)據(jù)庫地址
String user = "root";//數(shù)據(jù)庫用戶名
String password = "root";//數(shù)據(jù)庫密碼
// 3)建立連接
Connection conn = DriverManager.getConnection(url, user, password);
// 4)創(chuàng)建PreparedStatement(動態(tài)) Statement(靜態(tài)的)(拼sql語句)
Statement st = conn.createStatement();
// 5)執(zhí)行數(shù)據(jù)庫命令(crud)
// executeUpdate insert update delete
// executeQuery select 用于查詢
ResultSet rs = st.executeQuery("select * from emp where deptno=10");// 用ResultSet接,返回結(jié)果集
// 6)結(jié)果的處理
while (rs.next()) {// 不知道有幾條 用while循環(huán)
// 雙引號里是數(shù)據(jù)庫的字段名
int emp_no = rs.getInt("empno");
System.out.println(emp_no);
String e_name = rs.getString("ename");
System.out.println(e_name);
int de_ptno = rs.getInt("deptno");
System.out.println(de_ptno);
}
// 7)關閉連接
rs.close();
st.close();
conn.close();
}
// 發(fā)現(xiàn)異常不顯示，改造代碼
@Test
public void select1() {
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
// 1）載入jdbc驅(qū)動(指定我要連接到哪種數(shù)據(jù)庫，連接不同數(shù)據(jù)庫用不同驅(qū)動)
Class.forName("com.mysql.jdbc.Driver");
// 2）定義連接url(連接準備 url,端口，用戶名，密碼)
String url = "jdbc:mysql://localhost:3306/java16?characterEncoding=UTF-8&useUnicode=true";
String user = "root";
String password = "root";
// 3）建立連接
conn = DriverManager.getConnection(url, user, password);
// 4）創(chuàng)建PreparedStatement（動態(tài)） Statement(靜態(tài)的)(拼sql語句)
st = conn.createStatement();
// 5）執(zhí)行數(shù)據(jù)庫命令（crud)
// executeUpdate insert update delete
// executeQuery select
// 查詢10號部門的員工的員工編號，員工姓名，部門編號
rs = st.executeQuery("select empno,ename,deptno from emp where deptno=10");
// 6）結(jié)果的處理
while (rs.next()) {
// 雙引號里是數(shù)據(jù)庫里的字段名，
String e_name = rs.getString("ename");
System.out.println(e_name);
int emp_no = rs.getInt("empno");
System.out.println(emp_no);
int deptno = rs.getInt("deptno");
System.out.println(deptno);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
// 7）關閉連接
try {
rs.close();
st.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
// 刪除 delete from 表名 where 字段名=''
@Test
public void del() {
Connection conn = null;
Statement st = null;
try {
// 1）載入jdbc驅(qū)動(指定我要連接到哪種數(shù)據(jù)庫，連接不同數(shù)據(jù)庫用不同驅(qū)動)
Class.forName("com.mysql.jdbc.Driver");
// 2）定義連接url(連接準備 url,端口，用戶名，密碼)
String url = "jdbc:mysql://localhost:3306/java16?characterEncoding=UTF-8&useUnicode=true";
String user = "root";
String password = "root";
// 3）建立連接
conn = DriverManager.getConnection(url, user, password);
// 4）創(chuàng)建PreparedStatement（動態(tài)） Statement(靜態(tài)的)(拼sql語句)
st = conn.createStatement();
// 5）執(zhí)行數(shù)據(jù)庫命令（crud)
// executeUpdate insert update delete
// executeQuery select
// 查詢10號部門的員工的員工編號，員工姓名，部門編號
int count = st.executeUpdate("delete from emp_copy where ename='SCOTT'");
System.out.println(count);
} catch (Exception e) {
e.printStackTrace();
} finally {
// 7）關閉連接
try {
st.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
// 修改 update 表名 set 字段名='' where 字段名=
@Test
public void update() throws ClassNotFoundException, SQLException {
Connection conn = null;
Statement st = null;
try {
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/java16?characterEncoding=UTF-8&useUnicode=true";
String user = "root";
String password = "root";
conn = DriverManager.getConnection(url, user, password);
st = conn.createStatement();
int count = st.executeUpdate("update dept_copy set dname='java70' where dname='70'");
System.out.println(count);
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
st.close();
conn.close();
} catch (SQLException e2) {
e2.printStackTrace();
}
}
}
// 添加 insert into 表名(,,) values(,,)
@Test
public void insert() throws ClassNotFoundException, SQLException {
Connection conn = null;
Statement st = null;
try {
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/java16?characterEncoding=UTF-8&useUnicode=true";
String user = "root";
String password = "root";
conn = DriverManager.getConnection(url, user, password);
st = conn.createStatement();
int count = st.executeUpdate("insert into dept_copy(deptno,dname) values(50,'cx')");
System.out.println(count);
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
st.close();
conn.close();
} catch (Exception e2) {
e2.printStackTrace();
}
}
}
}
