文章目录
1.一个简单的jdbc连接实例
JDBC类似于一个接口,用于和数据库建立连接的一套api,各家厂商,想要建立连接,就需要实现jdbc这套规范,数据库写的实现类称之为数据库驱动。
jdbc的过程:
- 导入数据库厂商的驱动
- 加载驱动(通过反射)
- 建立连接(DriverManager.getConnection())
- 获取操作对象()
- 编写SQL语句
- 执行SQL语句
- 释放资源
URL:统一资源定位符
代码如下:
package demo;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class jdbcDemo {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//1.导入数据库厂商写的驱动类
//2.利用反射,获取class类的对象
Class.forName("com.mysql.jdbc.Driver");
//3.获取链接对象
String url="jdbc:mysql://localhost:3306/work";
String username="root";
String password="123";
Connection connection = DriverManager.getConnection(url, username, password);
//4.获取操作对象
Statement statement = connection.createStatement();
//5.编写并执行sql语句
String sql="insert into zhixin values ('老师','200')";
int i = statement.executeUpdate(sql);
//返回值为是否操作成功
//6.判断是否加载成功
if (i>0){
System.out.println("操作数据库成功");
}else {
System.out.println("操作数据库失败");
}
//7.释放资源
connection.close();
statement.close();
}
}
2.sql注入的风险
public class Demo2 {
public static void main(String[] args) throws Exception{
//模拟登录时输入的账号和密码
String username="'1' or '1'='1'";
String password="'1' or '1'='1'";
//连接数据库进行连接
Class.forName("com.mysql.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "123456");
Statement statement = conn.createStatement();
String sql="select * from user where username="+username+" and password="+password;
ResultSet resultSet = statement.executeQuery(sql);
if (resultSet.next()){
System.out.println("登录成功");
}else {
System.out.println("登录失败");
}
}
}
结果为登录成功。
原因:因为传统的加载的过程中在调用数据库的过程中使用的拼串的方式,在调用sql语句的过程中会出现:
select * from 表名 where 列名=‘1’ or ‘1’=‘1’(true)
的问题出现。
防止出现sql的注入使用下面的方法:
public class Demo3 {
public static void main(String[] args) throws Exception {
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "123456");
String sql = "Select * from user where username=? and password=?";
PreparedStatement preparedStatement = conn.prepareStatement(sql);
//使用预处理的方式
preparedStatement.setString(1, "zhangsan");
preparedStatement.setString(2, "123456");
//括号中第一个数时?从左到右数的索引值,第二个值时?对应的值
ResultSet resultSet = preparedStatement.executeQuery();
//因为在预处理的过程中已经对数据库进行了操作,所以在执行sql语句时不需要再对数据库进行操作
if (resultSet.next()) {
System.out.println("登录成功");
} else {
System.out.println("登录失败");
}
}
}
3.jdbc连接数据库,将数据库中的数据封装在类中
封装类:
package com.ge.demo1;
public class user {
private String username;
private String password;
public user(String username, String password) {
this.username = username;
this.password = password;
}
@Override
public String toString() {
return "user{" +
"username='" + username + '\'' +
", password='" + password + '\'' +
'}';
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
}
jdbc:
public class Demo1 {
public static void main(String[] args) throws Exception {
Class.forName("com.mysql.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "123456");
Statement statement = conn.createStatement();
String sql="Select * from user";
ResultSet resultSet = statement.executeQuery(sql);
ArrayList<user> list = new ArrayList<>();
//创建一个集合将对象封装起来
while (resultSet.next()){
//获取数据中的数据,可以使用行列数,也可以使用列名
String username = resultSet.getString(1);
String password = resultSet.getString("password");
user user = new user(username, password);
list.add(user);
}
System.out.println(list);
}
}
显示结果:
[user{username='zhangsan', password='123456'}, user{username='lisi', password='123456'}, user{username='wangwu', password='123456'}]
使用防止sql注入的方式进行封装数据库中的对象
public class Demo4 {
public static void main(String[] args) throws Exception{
Class.forName("com.mysql.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "123456");
String sql="select * from user";
PreparedStatement preparedStatement = conn.prepareStatement(sql);
ResultSet resultSet = preparedStatement.executeQuery();
ArrayList<user> list = new ArrayList<>();
while (resultSet.next()){
String username = resultSet.getString(1);
String password = resultSet.getString(2);
user user = new user(username, password);
list.add(user);
}
System.out.println(list);
}
}
4.创建配置文件,利用配置文件实现与java与数据库之间的连接
public class Demo5 {
public static void main(String[] args) throws Exception {
Properties properties = new Properties();
properties.load(new FileReader("peizhi.properties"));
String url = properties.getProperty("url");
String username = properties.getProperty("username");
String password = properties.getProperty("password");
Connection connection = DriverManager.getConnection(url, username, password);
Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery("Select * from user");
while (resultSet.next()){
String uname = resultSet.getString(1);
String pword = resultSet.getString(2);
System.out.println(uname+"==="+pword);
}
}
}
配置文件(peizhi.properties):
url=jdbc:mysql://localhost:3306/test
username=root
password=123456
注意配置文件是通过等号连接的字符串,等号两边没有引号
很多时候配置文件的路径不是写死的这个时候有下面的两种方法:
1.ResourceBundle 这个类,读取的配置文件,有3个要求:
1.配置文件要在src 下
2.配置文件的后缀名,必须为 .properties
3.文件的后缀名,你在读取的时候,不要写
ResourceBundle bundle = ResourceBundle.getBundle("peizhi");
String url = bundle.getString("url");
String username = bundle.getString("username");
String password = bundle.getString("password");
2.读取src下的配置文件
InputStream resourceAsStream = Demo.class.getClassLoader().getResourceAsStream("peizhi.properties");
Properties properties = new Properties();
properties.load(resourceAsStream);
5.封装工具类(jdbc连接与释放):
public class Utilstool {
private static String driver;
private static String url;
private static String username;
private static String password;
static {
try {
Properties properties = new Properties();
properties.load(new FileReader("peizhi.properties"));
driver = properties.getProperty("driver");
url = properties.getProperty("url");
username = properties.getProperty("username");
password = properties.getProperty("password");
} catch (IOException e) {
e.printStackTrace();
}
}
//私有化工具类,防止被创建对象
private Utilstool() {
}
//创建获取Connection对象的方法
public static Connection getconn() throws Exception {
Class.forName(driver);
Connection connection = DriverManager.getConnection(url, username, password);
return connection;
}
//创建释放内存的方法(DML使用)
public static void close(Connection conn, PreparedStatement preparedStatement) {
try {
if (conn != null) {
conn.close();
}
if (preparedStatement != null) {
preparedStatement.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
//重载方法(DQL使用)
public static void close(Connection conn, PreparedStatement preparedStatement, ResultSet resultSet) {
try {
if (conn != null) {
conn.close();
}
if (preparedStatement != null) {
preparedStatement.close();
}
if (resultSet != null) {
resultSet.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
6.大量往数据库中添加数据(批处理):
public class Demo7 {
public static void main(String[] args) throws Exception {
ArrayList<U> list = new ArrayList<>();
for (int i = 1; i <= 1000; i++) {
U u = new U(i, "123456");
list.add(u);
}
Connection conn = Utilstool.getconn();
String sql="insert into u values(?,?)";
PreparedStatement preparedStatement = conn.prepareStatement(sql);
for (U u : list) {
preparedStatement.setInt(1,u.getId());
preparedStatement.setString(2,u.getUsername());
preparedStatement.executeUpdate();
}
}
Utilstool.close(conn,preparedStatement);
}
上面的这种做法,在for循环里面每次都要和数据库建立连接,影响效率
public class Demo7 {
public static void main(String[] args) throws Exception {
ArrayList<U> list = new ArrayList<>();
for (int i = 1; i <= 1000; i++) {
U u = new U(i, "123456");
list.add(u);
}
Connection conn = Utilstool.getconn();
String sql="insert into u values(?,?)";
PreparedStatement preparedStatement = conn.prepareStatement(sql);
for (U u : list) {
preparedStatement.setInt(1,u.getId());
preparedStatement.setString(2,u.getUsername());
preparedStatement.addBatch();
//添加批处理
}
preparedStatement.executeBatch();
//执行批处理
preparedStatement.clearBatch();
//结束批处理
Utilstool.close(conn,preparedStatement);
}
}
7.调用存储过程,和自定义函数
{?= call <procedure-name>[(<arg1>,<arg2>, ...)]} -- 调用自定义函数
{call <procedure-name>[(<arg1>,<arg2>, ...)]} -- 调用存储过程
1.调用存储过程
sql存储过程函数如下:
DELIMITER $$
CREATE
PROCEDURE `test`.`mygc`(IN num INT ,OUT r INT)
BEGIN
DELETE FROM testtable WHERE id=num;
SELECT COUNT(*)FROM testtable INTO r;
END$$
DELIMITER ;
sql表:
id | uusername |
---|---|
1 | zhangsan |
2 | lisi |
3 | wangwu |
sql调用(删除id为3的记录并统计剩下的记录数):
CALL mygc(3,@rr)
SELECT @rr
java调用如下:
public class Demo8 {
public static void main(String[] args) throws Exception {
//建立连接
Connection conn = Utilstool.getconn();
String sql="{call mygc(?,?)}";
CallableStatement callableStatement = conn.prepareCall(sql);
//将第一问号的值作为输入项,输入的值为3
callableStatement.setInt(1,3);
//获取输出项,第二个问号的输类型为Iteger类型
callableStatement.registerOutParameter(2, Types.INTEGER);
//执行sql操作
callableStatement.execute();
//获取返回值
int rr = callableStatement.getInt(2);
System.out.println(rr);
//释放空间
Utilstool.close(conn,callableStatement);
}
}
2.调用自定义函数
sql中的自定义函数如下:
DELIMITER $$
CREATE
FUNCTION `test`.`myfun`(num INT)
RETURNS INT
BEGIN
SELECT username FROM u WHERE id=num INTO num;
RETURN num;
END$$
DELIMITER ;
sql调用:
SELECT myfun(2)
java的调用如下:
public class Demo9 {
public static void main(String[] args) throws Exception {
Connection conn = Utilstool.getconn();
String sql ="{?=call myfun(?)}";
CallableStatement callableStatement = conn.prepareCall(sql);
//设置输入参数
callableStatement.setInt(2,2);
//注册返回值
callableStatement.registerOutParameter(1, Types.VARCHAR);
//执行操作函数
callableStatement.execute();
//获取返回值(第一个问号的值)
int r = callableStatement.getInt(1);
System.out.println("函数的返回值是"+r);
//释放资源
Utilstool.close(conn,callableStatement);
}
}
结果为String类型的0
3.利用调用自定义函数的方法抽取md5加密函数
public class MD5 {
private MD5() {
}
public static String getmd5(String password) throws Exception {
Connection conn = Utilstool.getconn();
String sql = "{?=call md5(?)}";
CallableStatement callableStatement = conn.prepareCall(sql);
callableStatement.setString(2, password);
callableStatement.registerOutParameter(1, Types.VARCHAR);
callableStatement.execute();
String newpwd = callableStatement.getString(1);
Utilstool.close(conn,callableStatement);
return newpwd;
}
}
8.获取自增长键的值
对数据库的操作:添加一条记录,并获取添加的记录中的自增长键的值
public class test1 {
public static void main(String[] args) throws Exception {
Connection conn = Utilstool.getconn();
String sql="insert into testtable(username) values(?)";
//新增的属性表示返回值为自增长键
PreparedStatement preparedStatement = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
preparedStatement.setString(1,"王力宏");
int i = preparedStatement.executeUpdate();
//获取自增长键
ResultSet generatedKeys = preparedStatement.getGeneratedKeys();
int id=0;
while (generatedKeys.next()){
id=generatedKeys.getInt(1);
}
System.out.println(id);
}
}
9.防止数据库中文乱码的问题
在设置jdbc的URL路径的时候
String url="jdbc:mysql://localhost:3306/jabc?useUnicode=true&charset=utf-8";