2022-03-28
Driver驱动类的位置
InsertUpdateDeleteTest类
package day0328;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class InsertUpdateDeleteTest {
public static void main(String[] args) throws Exception {
//1.注册驱动
Class.forName("com.mysql.cj.jdbc.Driver"); // 连接mysql数据库
//2.建立连接
//例如:http(超文本传输协议)://127.0.0.1(ip,本地地址):8080(端口)/index.html(首页)
//jdbc:mysql://127.0.0.1(或者localhost):3306/(数据库名)
String url = "jdbc:mysql://127.0.0.1:3306/homework?"
+ "serverTimezone=Asia/Shanghai&useSSL=false&characterEncoding=utf8"; //连接的地址:服务器的时间,时区,不用ssl协议,指定字符集
String user = "root";
String password = "root";
Connection conn = DriverManager.getConnection(url, user, password);
System.out.println(conn); //com.mysql.cj.jdbc.ConnectionImpl@68be2bc2
//3.操作数据
Statement stmt = conn.createStatement(); //创建数据库操作对象
// String sql = "insert into emp(no, name, age) "
// + "values('2022012', '李四', 18)";
// int count = stmt.executeUpdate(sql); //插入,更新,删除
// System.out.println("插入数据的条数:" + count);
String sql1 = "update emp set name = '王五' where name = '李四'";
String sql2 = "delete from emp where name = '??'";
int count1 = stmt.executeUpdate(sql1); //插入,更新,删除
int count2 = stmt.executeUpdate(sql2); //插入,更新,删除
System.out.println("更新数据的条数:" + count1);
System.out.println("删除数据的条数:" + count2);
//4.释放资源
stmt.close(); //后创建先关闭,依赖于前面先创建
conn.close(); //先创建后关闭
}
}
SelectTest类
package day0328;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class SelectTest {
public static void main(String[] args) throws Exception {
//1.注册驱动
Class.forName("com.mysql.cj.jdbc.Driver"); //Class是类,静态方法(反射机制),5.多的版本,不加cj
//2.建立连接
String url = "jdbc:mysql://127.0.0.1:3306/homework?"
+ "serverTimezone=Asia/Shanghai&useSSL=false&characterEncoding=utf8"; //连接的地址,characterEncoding=utf8指定字符集
String user = "root";
String password = "root";
Connection conn = DriverManager.getConnection(url, user, password);
//3.操作数据
//3.1 获取数据库操作对象
Statement stmt = conn.createStatement();
//3.2 执行SQL语句
String sql = "select no, name, age, joinDate from emp where age = 20";
ResultSet rs = stmt.executeQuery(sql); //执行查询语句,返回结果集
//3.3 处理结果集
//用rs.next()方法判断有无下一条结果,有的话,循环体内取出数据,同时游标(指针)
//向下移动一个位置,继续判断有无下一条结果
while (rs.next()) {
// String no = rs.getString(1);
// String name = rs.getString(2);
String no = rs.getString("no"); //直接指定列名
String name = rs.getString("name");
int age = rs.getInt("age"); //结果集用getInt()方法得到int类型
Date joinDate = rs.getDate("joinDate");
String dateString = null;
if (joinDate != null) {
dateString = joinDate.toString();
}
System.out.println(no + " , " + name + " , " + age + " , " + dateString);
}
//已婚未婚分组,查询总薪资
// String sql1 = "select sum(salary) from emp group by isMarried";
//4.释放资源
rs.close();
stmt.close();
conn.close();
}
}
2022-03-29
SelectTest1类
package day0329;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Scanner;
/**
* PreparedStatement&Statement类
* 与statement相比,有三点好处:
* 1.解决sql注入的问题
* 2.效率更高,PreparedStatement编译一次,执行多次,Statement每执行一次前要编译一次
* 3.帮助进行数据类型检查
* @author Katrina
*/
public class SelectTest1 {
public static void main(String[] args) throws Exception {
//1.注册驱动
Class.forName("com.mysql.cj.jdbc.Driver");
//2.建立连接
String url = "jdbc:mysql://localhost:3306/homework1?"
+ "serverTimezone=Asia/Shanghai&userSSL=false"
+ "&characterEncoding=utf8";
String user = "root";
String password = "root";
Connection conn = DriverManager.getConnection(url, user, password);
//3.操作数据
/*方法1:比第二种多了一步*/
//3.1获取预编译的数据库操作对象
//?占位符,编译时占一个位置,执行之前给它赋值(到后期在这个位置补全数值)
String sql = "select Sid, Cid, score from sc where Sid = ? and Cid = ?";
// String sql = "select Sid, Cid, score from sc where Sid = ?";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1, "01"); //填到第一个占位符的位置
// ps.setString(1, "01 or 1=1"); //不输出
ps.setString(2, "02");
//3.2执行SQL语句
ResultSet rs = ps.executeQuery(); //已经预编译sql,就不用再传入参数
/*方法2:*/
//3.1获取数据库操作对象
// Statement stmt = conn.createStatement();
//3.2执行SQL语句
/*
* 查询
* 例如:
* 查询sc成绩表学生id为01的学生id,所学课程及成绩
* 1.什么语句:查询
* 2.明确查询的结果: 学生id,所学课程及成绩
* 3.找到所求结果所在的表:sc
* 4.明确条件:学生id为01
* 5.select 结果 from 表 where 条件
*/
// Scanner scanner = new Scanner(System.in);
// System.out.println("请输入学生编号:");
// String id = scanner.next(); //'01'(一个字符串)
// String id = scanner.nextLine(); //'01' or 1=1(永远为真,条件无效)[一串字符串] SQL注入->解决方法
// String sql = "select Sid, Cid, score from sc where Sid = " + id;
// System.out.println(sql);
// ResultSet rs = stmt.executeQuery(sql);
//3.3处理SQL结果集
while (rs.next()) { //判断结果集是否有下一条结果
/*
* Java SQL
* int int()
* int tinyint() 0或1表示真或者假
* byte int()
* short int()
* String varchar()
* float decimal(10, 2)
* double decimal(10, 2)
* Date Date
*/
String sid = rs.getString("Sid");
String cid = rs.getString("Cid");
double score = rs.getDouble("score");
System.out.println(sid + "," + cid + "," + score);
}
//4.释放资源
rs.close();
// stmt.close();
ps.close(); //预编译数据库对象的资源释放
conn.close();
}
}
UpdateTest类
第一种写法:
package day0329;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class UpdateTest {
public static void main(String[] args) throws Exception {
Class.forName("com.mysql.cj.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/homework1?"
+ "serverTimezone=Asia/Shanghai&userSSL=false"
+ "&characterEncoding=utf8";
String user = "root";
String password = "root";
Connection conn = DriverManager.getConnection(url, user, password);
Statement stmt = conn.createStatement();
String sql = "update sc set score = 88 where Sid = '01'"; //Sid01成绩更新成88
int count = stmt.executeUpdate(sql);
System.out.println(count);
stmt.close();
conn.close();
}
}
第二种写法:
package day0329;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.Statement;
public class UpdateTest {
public static void main(String[] args) throws Exception {
Class.forName("com.mysql.cj.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/homework1?"
+ "serverTimezone=Asia/Shanghai&userSSL=false"
+ "&characterEncoding=utf8";
String user = "root";
String password = "root";
Connection conn = DriverManager.getConnection(url, user, password);
String sql = "update sc set score = 88 where Sid = ?"; //Sid01成绩更新成88
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1, "01");
int count = ps.executeUpdate();
System.out.println(count);
ps.close();
conn.close();
}
}
SelectTest2类
package day0329;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class SelectTest2 {
public static void main(String[] args) {
/*
* throws出现异常close访问不到,资源一致被占用
* 把可能出现的异常放入try-catch中,把释放资源放在finally里
* try{可能出现异常的语句} catch (捕获的异常类型 对象e)
* {输出异常信息} finally {一定执行的语句}
* 区别:throws相当于甩锅
* try-catch相当于尝试着把锅改正下
* 注意:涉及到资源的需要使用try-catch
*/
//一开始设置为null,生成再进行指向
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
//1.注册驱动
Class.forName("com.mysql.cj.jdbc.Driver");
//2.建立连接
String url = "jdbc:mysql://localhost:3306/homework1?"
+ "serverTimezone=Asia/Shanghai&userSSL=false"
+ "&characterEncoding=utf8";
String user = "root";
String password = "root";
conn = DriverManager.getConnection(url, user, password);
//3.操作数据
//3.1获取预编译的数据库操作对象
String sql = "select Sid, avg(score) as avg_score from sc "
+ "group by Sid order by avg_score";
ps = conn.prepareStatement(sql);
//3.2执行SQL语句
rs = ps.executeQuery();
//3.3处理结果集
while (rs.next()) {
String sid = rs.getString("Sid");
double avgScore = rs.getDouble("avg_score");
System.out.println(sid + "," + avgScore);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
//4.释放资源,也会报异常
//进行访问出现异常,重新访问即可
//资源释放一个出现异常,后面两个也要释放,所以使用多个try-catch
if (rs != null) { //资源确实存在,再用尝试关闭,尝试关闭也会报异常,需要进行异常捕获
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (ps != null) {
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}