JDBC连接数据库
JDBC连接数据库
什么是JDBC
Java Database Connectivity:Java访问数据库的解决方案;JDBC定义了一套标准接口,即访问数据库的通用API,不同的数据库厂商根据各自数据库的特点去实现这些接口;JDBC希望用相同的方式访问不同的数据库,让具体的数据库操作与数据库厂商实现无关,从而在不同的数据库之间轻易的进行切换。
JDBC相关接口与实现类
驱动接口:DriverManager
连接接口:Connection
语句对象接口:Statement
结果集接口:ResultSet
JDBC工作原理:
//加载驱动 Class.forName();
//获取Connection连接 DriverManager
//创建Statement执行语句
//返回ResultSet查询结果
//释放资源
下载对应的数据库的驱动及导包 :
下载地址:点击跳转下载地址
mysql-connector-java-5.0.4-bin.jar
在IDEA中将jar包导入:
导包完成、已经站上了巨人的肩膀。
连接数据库
//此处的异常都应该使用使用try-catch处理,(别问为啥没处理,问就是代码能跑起来)
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//1.加载驱动com.mysql.cj.jdbc.Driver.class
Class.forName("com.mysql.cj.jdbc.Driver");
//2.获取链接
//如下语可能会出现SSL与JDBC版本不一致问题
//String url = "jdbc:mysql://localhost:3306/myschool?serverTimezone=GMT";
//myschool数据库名
String url = "jdbc:mysql://localhost:3306/myschool?charset=utf8mb4&useSSL=false&useTimezone=true&serverTimezone=GMT%2B8&characterEncoding=utf-8&allowPublicKeyRetrieval=true";
//用户名
String user = "root";
//数据库密码
String pwd = "123456";
Connection conn = DriverManager.getConnection(url, user, pwd);
//3.释放资源
//如果不为空则证明该流使用过,因此才有关的意义(下面的就直接关了没有处理奥)
if (conn != null){
try {
conn.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
向数据库中添加记录
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//1.加载驱动
Class.forName("com.mysql.cj.jdbc.Driver");
//2.获取链接
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/myschool?serverTimezone=GMT", "root", "123456");
//3.创建数据库语言
String sql = "insert into teacher(Tid,Tname,Tsex,Tbirthday,Taddress,Temail,Tmoney) values (5,'赵六',1,'1994-03-04','新疆阿克苏','zhaoliu@qq.com',3000)";
Statement statement = conn.createStatement();
int i = statement.executeUpdate(sql);
if(i > 0){
System.out.println("添加成功");
}else{
System.out.println("添加失败");
}
statement.close();
conn.close();
}
数据库中删除字段:
public static void main(String[] args) throws ClassNotFoundException, SQLException {
Scanner scan = new Scanner(System.in);
//1.加载驱动
Class.forName("com.mysql.cj.jdbc.Driver");
//2.获取链接
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/myschool?serverTimezone=GMT", "root", "123456");
System.out.println("请输入编号:");
int Sid = scan.nextInt();
//3.创建数据库语言
String sql = "delete from student where Sid =" + Sid;
Statement statement = conn.createStatement();
//在增删改中返回的都是一个int的类型的值,代表操作受影响的行数
int i = statement.executeUpdate(sql);
//当返回值大于0时代表删除成功
if(i > 0){
System.out.println("删除成功");
}else{
System.out.println("删除失败");
}
//关闭资源
statement.close();
conn.close();
}
向数据库中添加字段:
public static void main(String[] args) throws ClassNotFoundException, SQLException {
Scanner scan = new Scanner(System.in);
Class.forName("com.mysql.cj.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/myschool?serverTimezone=GMT", "root", "123456");
System.out.println("请输入要修改的学生编号:");
int stuId = scan.nextInt();
System.out.println("请输入新的姓名:");
String Sname = scan.next();
System.out.println("请输入新的生日:(格式为:yyyy-MM-dd)");
String Sbirth = scan.next();
System.out.println("请输入性别:");
String Ssex = scan.next();
System.out.println("请输入班级编号:");
int SclassId = scan.nextInt();
String sql = "update student set sname = '"+ Sname +"', birthday = '"+ Sbirth +"', ssex = '"+ Ssex +"', classid = " + SclassId + " where Sid = " + stuId;
Statement state = conn.createStatement();
int i = state.executeUpdate(sql);
if(i > 0){
System.out.println("添加成功");
}else{
System.out.println("添加失败");
}
state.close();
conn.close();
}
查询表中的字段:
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//1.加载驱动
Class.forName("com.mysql.cj.jdbc.Driver");
//2.获取链接
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/myschool?serverTimezone=GMT", "root", "123456");
System.out.println("请输入编号:");
//3.创建数据库语言
String sql = "select * from student";
Statement statement = conn.createStatement();
//statement.executeQuery(sql)注意执行方法;增删改是executeupDate();
ResultSet resultSet = statement.executeQuery(sql);
while (resultSet.next()){
int sid = resultSet.getInt("sid");
String Sname = resultSet.getString("sname");
String Sbirthday = resultSet.getString("birthday");
String Ssex = resultSet.getString("ssex");
int classID = resultSet.getInt("classid");
System.out.println(sid + Sname + Sbirthday + Ssex + classID);
}
//注意关流的顺序
resultSet.close();
statement.close();
conn.close();
}
解决sql注入问题
public static void main(String[] args) throws ClassNotFoundException, SQLException {
Scanner input = new Scanner(System.in);
// 1. 加载驱动
Class.forName("com.mysql.cj.jdbc.Driver");
// 2. 获取连接
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/myschool?serverTimezone=GMT", "root", "123456");
System.out.println("请输入账号");
String username = input.nextLine();
System.out.println("请输入密码");
String userpwd = input.nextLine();
// 3. 登录 -- 将sql语句中的传参的地方用 ? 占位
//String sql = "select * from login where lusername='" + username +"' and luserpwd='" + userpwd +"'"
//将原来的字符串拼接转换为?占位符进行拼接
String sql = "select * from login where lusername=? and luserpwd=?";
// 3.1 执行对象
//Statement statm = conn.createStatement(); 过去时
// prepareStatement 将sql语句传入 -- 预处理sql
PreparedStatement prestatm = conn.prepareStatement(sql);
// 再使用此方法给sql语句传值
prestatm.setObject(1, username);
prestatm.setObject(2, userpwd);
// 3.2 执行
// ' or 1=1 -- sql注入
//ResultSet rs = statm.executeQuery(sql);
ResultSet rs = prestatm.executeQuery();
if(rs.next()) {
int lid = rs.getInt("lid");
String lusername = rs.getString("lusername");
String luserpwd = rs.getString("luserpwd");
String luname = rs.getString("luname");
String lusex = rs.getString("lusex");
String laddress = rs.getString("laddress");
System.out.println(lid+lusername+luserpwd+luname+lusex+laddress);
System.out.println("登录成功");
}else {
System.out.println("账号密码错误");
}
// 释放资源
rs.close();
// statm.close();
prestatm.close();
conn.close();
}
注意上述方法中存在隐患当输入的账号为 ' or 1 = 1 --
是存在sql注入问题,即会直接访问数据库中的字段(注意(’ or 1 = 1 --)括号中是一个整体)
select * from login where lusername=? and luserpwd=?
--当输入 ' or 1 = 1 -- 时
select * from login where lusername='" + ' or 1 = 1 -- +"' and luserpwd='" + userpwd +"'
安全的解决办法:将createStatement()转换为prepareStatement(sql)
解决原理:当出现sql注入问题时,应该在特殊字符前面加上\
转义字符,但是prepareStatement()已经封装了此方法,因此调用此方法就可以解决sql注入问题。
登录并返回个人信息
public static void main(String[] args) throws ClassNotFoundException, SQLException {
Scanner scan = new Scanner(System.in);
Class.forName("com.mysql.cj.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/myschool?serverTimezone=GMT", "root", "123456");
System.out.println("请输入用户名:");
String userName = scan.nextLine();
System.out.println("请输入密码:");
String userPwd = scan.nextLine();
//3.创建数据库语言
String sql = "select * from login where lusername = ? and luserpwd = ?";
PreparedStatement prep = conn.prepareStatement(sql);
prep.setObject(1,userName);
prep.setObject(2,userPwd);
ResultSet resultSet = prep.executeQuery();
while (resultSet.next()){
String sid = resultSet.getString("lid");
String Sname = resultSet.getString("lusername");
String Sbirthday = resultSet.getString("luserpwd");
String Ssex = resultSet.getString("luname");
String classID = resultSet.getString("lusex");
String laddress = resultSet.getString("laddress");
System.out.println(sid + Sname + Sbirthday + Ssex + classID + laddress);
}
resultSet.close();
prep.close();
conn.close();
}
解决sql注入后的数据库插入字段
public static void main(String[] args) throws ClassNotFoundException, SQLException {
Scanner scan = new Scanner(System.in);
Class.forName("com.mysql.cj.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/myschool?serverTimezone=GMT", "root", "123456");
System.out.println("请输入账号:");
String uid = scan.nextLine();
System.out.println("请输入密码:");
String upwd = scan.nextLine();
System.out.println("请输入姓名:");
String uName = scan.nextLine();
System.out.println("请输入性别:");
String usex = scan.nextLine();
System.out.println("请输入住址:");
String uaddress = scan.nextLine();
//3.创建数据库语言
String sql = "insert into login(lusername,luserpwd,luname,lusex,laddress) values (?,?,?,?,?)";
PreparedStatement prep = conn.prepareStatement(sql);
prep.setObject(1,uid);
prep.setObject(2,upwd);
prep.setObject(3,uName);
prep.setObject(4,usex);
prep.setObject(5,uaddress);
int i = prep.executeUpdate();
if (i > 0){
System.out.println("注册成功!!!");
}else{
System.out.println("注册失败!!!");
}
// resultSet.close();
prep.close();
conn.close();
}