JDBC初始+UPDATE操作
1.JDBC操作数据库的步骤
1.1.导入JDBC驱动
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.6</version>
</dependency>
或
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.21</version>
</dependency>
1.2.加载驱动类
版本是8.x的时候 “com.mysql.cj.jdbc.Driver”
版本是5.x的时候 “com.mysql.jdbc.Driver”
String className = "com.mysql.jdbc.Driver";
Class.forName(className);
1.3.获取连接
版本是8.x的时候 “jdbc:mysql://localhost:3306/newdb3?serverTimezone=CST”
版本是5.x的时候 “jdbc:mysql://localhost:3306/newdb3”
String url = "jdbc:mysql://localhost:3306/newdb3";
String user = "root";
String password = "root";
Connection conn = DriverManager.getConnection(url,user,password);
1.4.创建状态参数
Statement stat = conn.createStatement();
1.5.执行数据库操作
String sql = "INSERT INTO EMP(EMPNO,ENAME) VALUES('111','张三') ";
//增删改"executeUpdate(sql)"
//查询"executeQuery(sql)"
stat.executeUpdate(sql);
1.6.关闭连接
stat.close();
conn.close();
2.JDBC的一些小细节
2.1.版本不同时"加载驱动类"和"获取连接"不同的问题
2.2.加载驱动类的问题
第一种执行方式
String className = "com.mysql.jdbc.Driver";
Class.forName(className);
加载类就是执行这个类的静态块，静态块的代码如下
static {
try {
java.sql.DriverManager.registerDriver(new Driver());
} catch (SQLException E) {
throw new RuntimeException("Can't register driver!");
}
}
所以可以直接执行静态块里代码
第二种执行方式
java.sql.DriverManager.registerDriver(new Driver());
Driver类下的构造方法是空的，静态块代码如下
static {
try {
java.sql.DriverManager.registerDriver(new Driver());
} catch (SQLException E) {
throw new RuntimeException("Can't register driver!");
}
}
所以可以直接创建Driver对象
第三种执行方式
new Driver()
第四种执行方式，通过系统直接设置属性值完成
System.setProperty("jdbc.Driver","com.mysql.cj.jdbc.Driver");
第五种执行方式，什么都不写
DriverManager类下有一个静态块，静态块里执行了这个方法"loadInitialDrivers()",方法里会找寻串并加载驱动类
2.3.事务的操作
默认为自动提交事务,参数是true为自动提交,数为false为手动提交
conn.setAutoCommit(boolean autoCommit);
2.4.事务的隔离级别
conn.setTransactionIsolation(int level);
2.5.事务的提交/回滚/保存还原点/跳转至还原点
conn.commit();
conn.setSavepoint();
conn.rollback();
Savepoint savepoint = conn.setSavepoint(String name);
conn.rollback(Savepoint savepoint);
3.JDBC封装
3.1.JDBC查询单条记录的封装
public Atm selecttOne(String name) {
Atm atm = null;
Connection conn = null;
Statement stat = null;
ResultSet rs = null;
try {
Class.forName("com.mysql.cj.jdbc.Driver");
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?serverTimezone=CST", "root", "root");
stat = conn.createStatement();
rs = stat.executeQuery("SELECT * FROM ATM WHERE NAME='" + name + "'");
if (rs.next()) {
atm = new Atm(rs.getString("name"), rs.getString("pass"), rs.getFloat("money"));
}
} catch (Exception e) {
e.printStackTrace();
} finally {
if (rs != null) {
try {
rs.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (stat != null) {
try {
stat.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
return atm;
}
32.JDBC修改数据的封装
public void update(Atm atm) {
Connection conn = null;
Statement stat = null;
ResultSet rs = null;
try {
Class.forName("com.mysql.cj.jdbc.Driver");
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/aotian?serverTimezone=CST", "root", "root");
stat = conn.createStatement();
String sql = "UPDATE ATM SET ANAME = '"+atm.getAname()+" ' ";
StringBuffer sb = new StringBuffer(sql);
if(atm.getApass()!=null && atm.getApass()!=""){
sb.append(", APASS = '"+atm.getApass()+"'");
}
if(atm.getAmoney()!=null){
sb.append(", AMONEY = "+atm.getAmoney()+" ");
}
sb.append("WHERE ANAME = '"+atm.getAname()+"'");
System.out.println(sb.toString());
int count = stat.executeUpdate(sb.toString());
if(count==0){
throw new RuntimeException("数据库执行时产生异常");
}
} catch (Exception e) {
e.printStackTrace();
} finally {
if (rs != null) {
try {
rs.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (stat != null) {
try {
stat.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
4.模糊查询
4.1.方式一
public void testCase() {
try {
Class.forName("com.mysql.cj.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/newdb3?serverTimezone=CST","root","root");
PreparedStatement pstat = conn.prepareStatement("SELECT * FROM EMP WHERE ENAME LIKE ?");
pstat.setString(1,"%A%");
ResultSet set = pstat.executeQuery();
while (set.next()){
System.out.println(set.getInt("empno")+"-"+set.getString("ename"));
}
} catch (Exception e) {
e.printStackTrace();
}
//关闭。。。
}
4.2.方式二，更麻烦
public void testCase() {
try {
Class.forName("com.mysql.cj.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/newdb3?serverTimezone=CST","root","root");
PreparedStatement pstat = conn.prepareStatement("SELECT * FROM EMP WHERE ENAME LIKE \"%\"?\"%\"");
pstat.setString(1,"A");
ResultSet set = pstat.executeQuery();
while (set.next()){
System.out.println(set.getInt("empno")+"-"+set.getString("ename"));
}
} catch (Exception e) {
e.printStackTrace();
}
//关闭。。。
}
5.分页查询
//参数是页数和行数
public void testCase(int m,int n) {
try {
Class.forName("com.mysql.cj.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/newdb3?serverTimezone=CST","root","root");
PreparedStatement pstat1 = conn.prepareStatement("SELECT * FROM EMP ORDER BY EMPNO LIMIT ?,?");
pstat1.setInt(1,5*(m-1));
pstat1.setInt(2,n);
ResultSet set1 = pstat1.executeQuery();
while (set1.next()){
System.out.println(set1.getInt("empno")+"-"+set1.getString("ename"));
}
} catch (Exception e) {
e.printStackTrace();
}
//关闭。。。
}
6.联合查询
public void testCase(int m,int n) {
try {
Class.forName("com.mysql.cj.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/newdb3?serverTimezone=CST","root","root");
PreparedStatement pstat1 = conn.prepareStatement("SELECT EMP.*,DEPT.DNAME,DEPT.LOC FROM EMP JOIN DEPT ON EMP.DEPTNO = DEPT.DEPTNO ORDER BY EMPNO LIMIT ?,?");
pstat1.setInt(1,5*(m-1));
pstat1.setInt(2,n);
ResultSet set1 = pstat1.executeQuery();
while (set1.next()){
Emp emp = new Emp(set1.getInt("empno"),set1.getString("ename"),set1.getString("job"),set1.getInt("mgr"),set1.getDate("hiredate"),
set1.getDouble("sal"),set1.getDouble("comm"),new Dept(set1.getInt("deptno"),set1.getString("dname"),set1.getString("LOC")));
System.out.println(emp);
}
} catch (Exception e) {
e.printStackTrace();
}
//关闭。。。
}
1430
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
