1、数据库JDBC
1.1、JDBC
JDBC:Java操作数据库的规范
1.2、第一个jdbc程序
创建测试数据库
CREATE DATABASE `jdbcStudy` CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `jdbcStudy`;
CREATE TABLE `users`(
`id` INT PRIMARY KEY,
`NAME` VARCHAR(40),
`PASSWORD` VARCHAR(40),
`email` VARCHAR(60),
birthday DATE
);
INSERT INTO `users`(`id`,`NAME`,`PASSWORD`,`email`,`birthday`)
VALUES(1,'zhangsan','123456','zs@sina.com','1980-12-04'),
(2,'lisi','123456','lisi@sina.com','1981-12-04'),
(3,'wangwu','123456','wangwu@sina.com','1979-12-04')
1.创建一个普通项目
2.导入数据库驱动
3.编写测试代码
package com.wang.lesson01;
//我的第一个JDBC程序
import java.sql.*;
public class JdbcFirstDemo {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//1. 加载驱动
Class.forName("com.mysql.cj.jdbc.Driver");//固定写法
//2. 用户信息和url
String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true?characterEncoding=utf8&&uesSSL=true&&serverTimezone=UTC";
request.setCharacterEncoding("UTF-8");//解决mysql5.5中文乱码问题
String username = "root";
String password = "123456";
//3. 连接成功,返回数据库对象 connection代表数据库
Connection connection = DriverManager.getConnection(url, username, password);
//4. 执行SQL的对象 statement 执行SQL的对象
Statement statement = connection.createStatement();
//5. 执行SQL的对象 去执行SQL 可能存在结果,查看返回结果
String sql = "SELECT * FROM users";
ResultSet resultSet = statement.executeQuery(sql);//返回的结果集
while (resultSet.next()){
System.out.println("id=" + resultSet.getObject("id"));
System.out.println("NAME=" + resultSet.getObject("NAME"));
System.out.println("PASSWORD=" + resultSet.getObject("PASSWORD"));
System.out.println("email=" + resultSet.getObject("email"));
System.out.println("birthday=" + resultSet.getObject("birthday"));
System.out.println("=====================================================");
}
//6. 释放连接
resultSet.close();
statement.close();
connection.close();
}
}
步骤总结:
1.加载驱动
2.连接数据库 DriverManager
3.获取执行SQL的对象 Statement
4.获得返回的结果集
5.释放连接
解决mysql5.5中文乱码问题
在String url=“jdbc:mysql://localhost:3306”后面加上request.setCharacterEncoding(“UTF-8”);
DriverManager
//DriverManager.registerDriver(new com.mysql.jdbc.Driver());
Class.forName("com.mysql.cj.jdbc.Driver");//固定写法
Connection connection= DriverManager.getConnection(url,name,password);
//connection代表数据库
//数据库设置自动提交
//事务提交
//事务回滚
connection.rollback();
connection.commit();
connection.setAutoCommit();
URL
String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&&useSSL=true&&serverTimezone=UTC";
//mysql 默认3306
//jdbc:mysql://主机地址:端口号/数据库名?参数1&参数2&参数3
//Oracle 1521
//jdbc:oralce:thin:@localhost:1521:sid
statement 执行SQL的对象 pPrepareStatement 执行SQL的对象
String sql="SELECT * FROM users";//编写Sql
statement.executeQuery();//查询操作返回ReseltSet
statement.execute();//执行任何SQL
statement.executeUpdate();//更新,插入,删除,返回一个受影响的行数
ResultSet 查询的结果集,封装了所以的查询结果
获得指定的数据类型
ResultSet resultSet = statement.executeQuery(sql);//返回的结果集,结果集中封装了我们全部查询的结果
resultSet.getObject();//在不知道列类型下使用
resultSet.getString();//如果知道则指定使用
resultSet.getInt();
遍历,指针
resultSet.next(); //移动到下一个
resultSet.afterLast();//移动到最后
resultSet.beforeFirst();//移动到最前面
resultSet.previous();//移动到前一行
resultSet.absolute(row);//移动到指定行
释放内存
resultSet.close();
statement.close();
connection.close();//耗资源
1.3、statement对象
Jdbc中的statement对象用于向数据库发送SQL语句,想完成对数据库的增删改查,只需要通过这个对象向数据库发送增删改查语句即可。
Statement对象的executeUpdate方法,用于向数据库发送增、删、改的sq|语句, executeUpdate执行完后, 将会返回一个整数(即增删改语句导致了数据库几行数据发生了变化)。
Statement.executeQuery方法用于向数据库发生查询语句,executeQuery方法返回代表查询结果的ResultSet对象。
CRUD操作-create
使用executeUpdate(String sql)方法完成数据添加操作,示例操作:
Statement statement = connection.createStatement();
String sql = "insert into user(...) values(...)";
int num = statement.executeUpdate(sql);
if(num>0){
System.out.println("插入成功");
}
CRUD操作-delete
使用executeUpdate(String sql)方法完成数据删除操作,示例操作:
Statement statement = connection.createStatement();
String sql = "delete from user where id =1";
int num = statement.executeUpdate(sql);
if(num>0){
System.out.println("删除成功");
}
CURD操作-update
使用executeUpdate(String sql)方法完成数据修改操作,示例操作:
Statement statement = connection.createStatement();
String sql = "update user set name ='' where name = ''";
int num = statement.executeUpdate(sql);
if(num>0){
System.out.println("修改成功");
}
CURD操作-read
使用executeQuery(String sql)方法完成数据查询操作,示例操作:
Statement statement = connection.createStatement();
String sql = "select * from user where id =1";
ResultSet rs= statement.executeQuery(sql);
while(rs.next()){
//根据获取列的数据类型,分别调用rs的相应发方法映射到Java对象中
}
代码实现
1、提取工具类
package com.wang.lesson02.utils;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
public class JdbcUtils {
private static String driver = null;
private static String url = null;
private static String username = null;
private static String password = null;
static {
try {
InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");
Properties properties = new Properties();
properties.load(in);
driver = properties.getProperty("driver");
url = properties.getProperty("url");
username = properties.getProperty("username");
password = properties.getProperty("password");
//1.驱动只用加载一次
Class.forName(driver);
} catch (IOException | ClassNotFoundException e) {
e.printStackTrace();
}
}
//获取连接
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(url, username, password);
}
//释放连接资源
public static void release(Connection conn, Statement st, ResultSet rs){
if (rs !=null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (st!=null) {
try {
st.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn!=null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
2、编写增删改的方法,executeUpdate
新增
package com.wang.lesson02;
import com.wang.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestIndert {
public static void main(String[] args) throws Exception {
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();//获取数据库连接
st = conn.createStatement();//获取SQL的执行对象
String sql = "INSERT INTO users(id,`NAME`,`PASSWORD`,`email`,`birthday`)" +
"VALUES(4,'xueshuai','123456','123456789@qq.com','2021-04-23')";
int i = st.executeUpdate(sql);
if (i>0) {
System.out.println("插入成功!");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}
删除
package com.wang.lesson02;
import com.wang.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestDelete {
public static void main(String[] args) throws Exception {
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();//获取数据库连接
st = conn.createStatement();//获取SQL的执行对象
String sql = "DELETE FROM users WHERE id = 4";
int i = st.executeUpdate(sql);
if (i>0) {
System.out.println("删除成功!");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}
更新
package com.wang.lesson02;
import com.wang.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestUpdate {
public static void main(String[] args) throws Exception {
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();//获取数据库连接
st = conn.createStatement();//获取SQL的执行对象
String sql = "UPDATE users SET `NAME`='xueshuai' ,`email`='2519736098@qq.com'WHERE id=1";
int i = st.executeUpdate(sql);
if (i>0) {
System.out.println("更新成功!");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}
3、查询
package com.wang.lesson02;
import com.wang.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestSelect {
public static void main(String[] args) {
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
st = conn.createStatement();
//SQL
String sql = "select * from users where id = 1";
rs = st.executeQuery(sql);//查询完毕会返回一个结果集
if (rs.next()){
System.out.println(rs.getString("NAME"));
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}
SQL注入的问题
sql存在漏洞,会被攻击导致数据泄露 SQL会被拼接 or
1.4、PreparedStatement对象
PreparedStatement可以防止SQL注入,并且效率更高
1、新增
package com.wang.lesson03;
import com.wang.lesson02.utils.JdbcUtils;
import java.sql.*;
import java.util.Date;
public class TestInsert {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement pst = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
//区别
//使用?占位符代替参数
String sql = "INSERT INTO users(id,`NAME`,`PASSWORD`,`email`,`birthday`)values(?,?,?,?,?)";
pst = conn.prepareStatement(sql);//预编译sql,先写sql,然后不执行
//手动给参数赋值
pst.setInt(1,5);
pst.setString(2,"xuediao");
pst.setString(3,"123456");
pst.setString(4,"123456@qq.com");
//注意点 :sql.Date 数据库 java.sql.Date()
// util.Date Java new Date().getTime() 获得时间戳
pst.setDate(5,new java.sql.Date(new Date().getTime()));
int i = pst.executeUpdate();
if (i>0){
System.out.println("插入成功");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(conn,pst,rs);
}
}
}
2、删除
package com.wang.lesson03;
import com.wang.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
public class TestDelete {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement pst = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
//区别
//使用?占位符代替参数
String sql = "delete from users where id=?";
pst = conn.prepareStatement(sql);//预编译sql,先写sql,然后不执行
//手动给参数赋值
pst.setInt(1,5);
//执行
int i = pst.executeUpdate();
if (i>0){
System.out.println("删除成功");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(conn,pst,rs);
}
}
}
3、更新
package com.wang.lesson03;
import com.wang.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class TestUpdate {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement pst = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
//区别
//使用?占位符代替参数
String sql = "update users set `NAME` =? where id=?;";
pst = conn.prepareStatement(sql);//预编译sql,先写sql,然后不执行
//手动给参数赋值
pst.setString(1,"薛帅");
pst.setInt(2,1);
//执行
int i = pst.executeUpdate();
if (i>0){
System.out.println("更新成功");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(conn,pst,rs);
}
}
}
4、查询
package com.wang.lesson03;
import com.wang.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class TestSelect {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement pst = null;
ResultSet rs =null;
try {
conn = JdbcUtils.getConnection();
String sql = "select * from users where id = ?";// 编写SQL
pst = conn.prepareStatement(sql);//预编译
pst.setInt(1,1);//传递参数
rs = pst.executeQuery();//执行
if (rs.next()){
System.out.println(rs.getString("NAME"));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtils.release(conn,pst,rs);
}
}
}
**PreparedStatement 防止SQL注入的本质 **:把传递进来的参数当作字符
PreparedStatement pst = null;
ResultSet rs =null;
try {
conn = JdbcUtils.getConnection();
String sql = "select * from users where id = ?";// 编写SQL
pst = conn.prepareStatement(sql);//预编译
pst.setInt(1,1);//传递参数
rs = pst.executeQuery();//执行
if (rs.next()){
System.out.println(rs.getString("NAME"));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtils.release(conn,pst,rs);
}
}
}
**PreparedStatement 防止SQL注入的本质 **:把传递进来的参数当作字符
假设其中存在穿衣字符,会直接被转义