3.使用JDBC操作数据库
JDBC
- JDBC
- Java数据库连接技术(Java DataBase Connectivity),能实现Java程序对各种数据库的访问
- 由一组使用Java语言编写的类和接口(JDBC API)组成,它们位于java.sql以及javax.sql中
JDBC访问数据库步骤
1、Class.forName()加载驱动
Class.forName("com.mysql.jdbc.Driver");
常用的数据库驱动
com.microsoft.jdbc.sqlserver.SQLServerDriver(SQL Server)
com.mysql.jdbc.Driver(MySql)
oracle.jdbc.driver.OracleDriver(Oracle)
2、DriverManager获取Connection连接
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/数据库名?characterEncoding=UTF-8","账号","密码");
3、创建Statement执行SQL语句
Statement stat = conn.createStatement();
Statement常用对象
ResultSet executeQuery(sql)
执行sql查询语句,并返回ResultSet对象
int executeUpdate(sql)
执行insert,update,delete语句,返回受影响行数
boolean execute(sql)
执行insert,update,delete语句,返回true或false false成功
防止数据库乱码及日期出错
放入url连接路径之后
?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull
4、返回ResultSet查询结果
String sql = "select * from 表名";
ResultSet rs = stat.executeQuery(sql); //这是一个查询语句
5、释放资源
rs.close();
stat.close();
conn.close();
格式问题
当涉及日期字段时,可能会报错,报错信息为:java.sql.SQLException: Cannot convert value ‘0000-00-00 00:00:00’ from column 7 to TIMESTAMP。
原因: JAVA连接MySQL数据库,在操作值为0的timestamp
类型时不能正确的处理,而是抛出一个异常
**解决方法:**在JDBC连接串中有一项属性:zeroDateTimeBehavior
,用来配置出现情况时的处理策略,该属性有三个属性值:
- exception:默认值,即抛出SQL state [S1009]. Cannot convert value…的异常;
- convertToNull:将日期转换成NULL值;
- round:替换成最近的日期即0001-01-01;
//在获取连接(DriverManager.getConnection)的url中添加
zeroDateTimeBehavior=convertToNull
//示例:
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/数据库名?characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull","账号","密码");
Statement与PreparedStatement区别
- Statement由方法createStatement()创建,该对象用于发送简单的SQL语句
- PreparedStatement由方法prepareStatement()创建,该对象用于发送带有一个或者多个输入参数的SQL语句
- SQL语句使用“?”作为数据占位符
- 使用setXxx()方法设置数据
- PreparedStatement—预编译
- 效率、性能、开销
- 安全性
- 代码可读性
示例:
//PreparedStatement可替换变量(在SQL语句中可以包含?)
String sql = "select * from user where id=?";
PreparedStatement ps = conn.prepareStatement(sql);
int id = 1001;
ps.setInt(1,id);
ResultSet rs = ps.executeQuery();
/**==================平平无奇的分割线====================*/
//Statement用法
int id = 1001;
String sql = "select * from user where id="+id;
Statement stmt = conn.CreateStatement();
ResultSet rs = stmt.executeQuery(sql);
总结-JDBC工作原理及内容
- JDBC的内容
- JDBC API
- 定义了一系列的接口和类,集成在java.sql和javax.sql包中
- DriverManager
- 管理各种不同的JDBC驱动
- JDBC 驱动
- 负责连接不同类型的数据库
小练习
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-BsOQweoN-1664034415440)(G:\笔记文档\mdp\javaweb\3\JDBC练习题.jpg)]
查询操作
- 1、可以查出指定月份注册的用户
- 2、根据ID查出指定用户的信息
- 3、查出积分大于某个值的用户信息
代码:
/**
*
* @author
* 查询操作:
* 1、可以查出指定月份注册的用户
* 2、根据ID查出指定用户的信息
* 3、查出积分大于某个值的用户信息
*
*/
public class SelectInfo {
/**
*
* @param args
*/
public static void main(String[] args) {
Scanner sc = new Scanner(System.in);
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
//1.导入jar包
//2.加载驱动
Class.forName("com.mysql.jdbc.Driver");
//3.获取连接
conn = DriverManager.getConnection("jdbc:mysql://localhost/myschool?characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull","root","root");
//4.获取statement对象
System.out.println("-------------查询操作:1、可以查出指定月份注册的用户------------");
String sql = "select * from user where month(time) = ?";
ps = conn.prepareStatement(sql);
System.out.print("请输入月份:");
int month = sc.nextInt();
//5.执行sql语句,并接收结果
ps.setInt(1, month);
rs = ps.executeQuery();
//6.处理结果
while(rs.next()) {
System.out.println("用户id" + "\t" + "用户昵称" + "\t" + " 注册时间" + "\t" + "积分" + "\t" + "性别");
System.out.println(rs.getInt("id") + "\t" + rs.getString("name") + "\t" + rs.getDate("time") + "\t" + rs.getInt("score") + "\t" + rs.getString("sex"));
}
System.out.println("-------------2、根据ID查出指定用户的信息------------");
sql = "select * from user where id = ?";
ps = conn.prepareStatement(sql);
System.out.print("请输入id:");
String id = sc.next();
ps.setString(1, id);
rs = ps.executeQuery();
//6.处理结果
while(rs.next()) {
System.out.println("用户id" + "\t" + "用户昵称" + "\t" + " 注册时间" + "\t" + "积分" + "\t" + "性别");
System.out.println(rs.getInt("id") + "\t" + rs.getString("name") + "\t" + rs.getDate("time") + "\t" + rs.getInt("score") + "\t" + rs.getString("sex"));
}
System.out.println("-------------3、查出积分大于某个值的用户信息------------");
sql = "select * from user where score > ?";
ps = conn.prepareStatement(sql);
System.out.print("请输入积分:");
int score = sc.nextInt();
ps.setInt(1, score);
rs = ps.executeQuery();
//6.处理结果
while(rs.next()) {
System.out.println("用户id" + "\t" + "用户昵称" + "\t" + " 注册时间" + "\t" + "积分" + "\t" + "性别");
System.out.println(rs.getInt("id") + "\t" + rs.getString("name") + "\t" + rs.getDate("time") + "\t" + rs.getInt("score") + "\t" + rs.getString("sex"));
}
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
try {
//7.释放资源
rs.close();
ps.close();
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
修改操作
- 可以修改指定用户的积分
代码:
/**
*
* @author
* 修改操作:可以修改指定用户的积分
*
*/
public class UpdateInfo {
/**
*
* @param args
* @throws Exception
*/
public static void main(String[] args) throws Exception {
Scanner sc = new Scanner(System.in);
Class.forName("com.mysql.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/myschool?characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull","root","root");
System.out.println("-------------修改操作:可以修改指定用户的积分------------");
System.out.print("请输入要修改积分的用户名:");
String name = sc.next();
System.out.print("请输入修改后的积分数:");
int score = sc.nextInt();
String sql = "update user set score=? where name=?";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setInt(1, score);
ps.setString(2, name);
boolean n = ps.execute();
if (n == false) {
System.out.println("修改成功!");
} else {
System.out.println("修改失败。");
}
}
}
删除操作
- 根据用户ID进行删除操作
代码:
/**
*
* @author
* 删除操作:根据用户ID进行删除操作
*
*/
public class DeleteInfo {
/**
*
* @param args
* @throws Exception
*/
public static void main(String[] args) throws Exception {
Scanner sc = new Scanner(System.in);
Class.forName("com.mysql.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/myschool?characrterEncoding=utf-8&zeroDateTimeBehavior=convertToNull","root","root");
System.out.println("-------------删除操作:根据用户ID进行删除操作------------");
System.out.print("请输入要删除的id:");
int id = sc.nextInt();
String sql = "delete from user where id=?";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setInt(1, id);
boolean n = ps.execute();
if (n == false) {
System.out.println("删除成功!");
} else {
System.out.println("删除失败。");
}
}
}
增加操作
- 使用循环和随机数技巧,增加1000个数据,
- 要求积分在0-200,
- 注册时间均匀分布在2018年各个月份,
- 从26个字母中随机取3个字母作为昵称,昵称不能一样。ID自增。
代码:
/**
*
* @author
* 增加操作:使用循环和随机数技巧,增加1000个数据,
* 要求积分在0-200,
* 注册时间均匀分布在2018年各个月份,
* 从26个字母中随机取3个字母作为昵称,昵称不能一样。ID自增。
*
*/
public class InsertInfo {
/**
*
* @param args
* @throws Exception
*/
public static void main(String[] args) throws Exception {
Class.forName("com.mysql.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/myschool?characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull","root","root");
String sql = "insert into user (id,name,time,score,sex) values (?,?,?,?,?)";
PreparedStatement ps = conn.prepareStatement(sql);
for (int i = 1004; i < 2004; i++) {
ps.setInt(1,i);
ps.setString(2,UtilTest.generateRandomStr(3));
ps.setString(3,UtilTest.RandomRegistrationTime());
ps.setInt(4,UtilTest.integralGet());
ps.setString(5,UtilTest.generateRandomSex());
boolean n = ps.execute();
if (n == false) {
System.out.println("添加第" + i + "条数据成功!");
} else {
System.out.println("添加第" + i + "条数据失败。");
}
}
}
}
添加信息工具类代码:
/**
* 添加信息工具类
* @author
*
*/
public class UtilTest {
//生成注册时间的方法
public static String RandomRegistrationTime(){
int year = 2018; //平年2月28天
Random randMonth = new Random();
int month = randMonth.nextInt(12)+1; //31天的月份
Random randHour = new Random();
int hour = randHour.nextInt(23);
Random randMin = new Random();
int minute = randMin.nextInt(60);
Random randSec = new Random();
int second = randSec.nextInt(60);
int Day = 0;
if (month==1 || month==3 || month==5 || month==7 || month==8 || month==10 || month==12) {
Random randDay = new Random();
Day = randDay.nextInt(31) + 1;
}
if (month==4 || month==6 || month==9 || month==11) {
Random randDay = new Random();
Day = randDay.nextInt(30) + 1;
}
if (month==2) {
Random randDay = new Random();
Day = randDay.nextInt(28) + 1;
}
return year+"-"+month+"-"+Day+" "+hour+":"+minute+":"+second;
}
//生成姓名的随机字符串的方法
public static String generateRandomStr(int len) {
//字符源,可以根据需要增减
String generateSource = "abcdefghijklmnopqrstuvwxyz";
String rtnStr = "";
for (int i = 0; i < len; i++) {
//循环随机获得当次字符,并移走选出的字符
String nowStr = String.valueOf(generateSource.charAt((int) Math.floor(Math.random() * generateSource.length())));
rtnStr += nowStr;
generateSource = generateSource.replaceAll(nowStr, "");
}
return rtnStr;
}
//积分的随机生成
public static int integralGet(){
//随机生成0-200之间的积分
Random n1 = new Random();
int number = n1.nextInt(200);
return number;
}
//性别的随机生成
public static String generateRandomSex(){
String random = "";
String[] doc = {"男","女"};
int index = (int) (Math.random()*doc.length);
random = doc[index];
return random;
}
}