MySQL常用函数及JDBC
一、MySQL常用函数
1.字符串函数
SELECT CONCAT("liu", "yan")
-- 带分隔符字符串连接函数:concat_ws(separator,[string|array(string)]+)
SELECT CONCAT_WS('.', 'www','51doit', 'com');
-- 字符串截取函数: substr(str, pos[, len])或者substring(str, pos[,len])
SELECT SUBSTR("angelababy", -2); -- pos是从1开始的索引，如果为负数则倒着数
SELECT SUBSTR("angelababy", 2, 2);
-- 字符串长度函数: length(str / binary)
SELECT LENGTH("I Love Hive");
-- 字符串反转函数: reverse
SELECT REVERSE("I Love Hive");
-- 字符串连接函数:concat(stri, str2, ... strN)
-- 字符串转大写函数: upper , ucase
SELECT UPPER("I Love Mysql");
SELECT UCASE("I Love Mysql");
-- 字符串转小写函数:lower , Lcase
SELECT LOWER("I Love Mysql");
SELECT LCASE("I Love Mysql");
-- 去空格函数: trim去除左右两边的空格
SELECT TRIM(" I Love Mysql ");
-- 左边去空格函数: ltrim
SELECT LTRIM(" I Love Mysql ");
-- 右边去空格函数:rtrim
SELECT RTRIM(" I Love Mysql ");
-- 空格字符串函数: space(n)返回指定个数空格
SELECT SPACE(4);
-- 重复字符串函数:repeat(str, n)重复str字符串n
SELECT REPEAT("I Love Mysql", 2);
-- 首字符ascii函数: ascii
SELECT ASCII("angela");
-- a对应ASCII 97
-- 左补足函数:lpad
SELECT LPAD('hi', 5, '*');
-- 右补足函数: rpad
SELECT RPAD('hi', 5, '*');
-- 集合查找函数:find_in_set(str ,str_array) 返回str的位置 从1开始
SELECT FIND_IN_SET('b', 'abc,b,ab,c,def');
2.日期函数
-- 当前日期
SELECT CURRENT_DATE();
-- 获取当前时问歌:current_timestamp
-- 同一查询中对current_timestamp 的所有调用均返回相同的值。
SELECT CURRENT_TIMESTAMP();
-- 获取当前UNIX时间戳函数:unix_timestamp
SELECT UNIX_TIMESTAMP();
-- 日期转UNIX时问戳函数: unix_timestamp
SELECT UNIX_TIMESTAMP("2022-08-13 19:08:03");
-- UNIX时间戳转日期函数:from_unixtime
SELECT FROM_UNIXTIME(1660417683);
SELECT FROM_UNIXTIME(0);
-- 日期比较函数; datediff日期格式要求'yyyy-MN-dd HH:mm:ss ' or 'yyyy-MM-dd '
SELECT DATEDIFF('2022-08-08', '2022-08-10');
-- 日期增加函数: date_add
SELECT DATE_ADD('2022-08-08', INTERVAL 10 DAY);
-- 日期减少函数: date_sub
SELECT DATE_SUB('2012-08-08', INTERVAL 10 DAY);
-- 获取年:year
SELECT YEAR('2022-08-13 19:08:03');
-- 获取月:month
SELECT MONTH('2022-08-13 19:08:03');
-- 获取日:day
SELECT DAY('2022-08-13 19:08:03');
-- 获取时:hour
SELECT HOUR('2022-08-13 19:08:03');
-- 获取分:minute
SELECT MINUTE('2022-08-13 19:08:03');
-- 获取秒:second
SELECT SECOND('2022-08-13 19:08:03');
-- 获取周:weekofyear 指定年份的第多少周
SELECT WEEKOFYEAR('2022-08-13 19:08:03');
select date('2022-08-13 19:08:03'); //只显示年月日
3.数学函数
-- 四舍五入: round返回double类型的整数值部分(四舍五入）
SELECT ROUND(3.1415926);
SELECT ROUND(3.1415926, 2);
-- 向下取整函数:floor
SELECT FLOOR(3.1415926);
SELECT FLOOR(-3.1415926);
-- 向上取整函数: ceil
SELECT CEIL(3.1415926);
SELECT CEIL(-3.1415926);
-- 取随机数函数: rand每次执行都不一样返回一个0到1范围内的随机数
SELECT RAND();
-- 在同一个查询中保证同一个随机数
SELECT RAND(10);
-- 二进制
SELECT BIN(100);
-- 转换进制 conv(num,现在进制,要转换的进制)
SELECT CONV(11, 10, 2);
-- 绝对值
SELECT ABS(-100);
4.条件判断函数
-- if判断 类似java三元表达式 if(boolean testCondion,T valueTrue,T valueFalse)
SELECT IF(1 = 2, 'a', 'b');
-- 判断是否为空: isnull 是null 返回1 不是null 返回0
SELECT ISNULL(NULL);
SELECT ISNULL(10);
-- coalesce(v1,v2,v3,v4...)返回第一个非空的值 如果都为null返回null
SELECT COALESCE(NULL, NULL, NULL, NULL);
SELECT COALESCE(NULL, NULL, 1, 2, 3, 4, NULL, 5, 6);
SELECT COALESCE(NULL, 3, 1, 2, 3, 4, NULL, 5, 6);
二、JDBC
1.JDBC概述
JDBC（Java DataBase Connectivity,java数据库连接）是一种用于执行SQL语句的Java API。JDBC是Java访问数据库的标准规范，可以为不同的关系型数据库提供统一访问，它由一组用Java语言编写的接口和类组成。
JDBC需要连接驱动，驱动是两个设备要进行通信，满足一定通信数据格式，数据格式由设备提供商规定，设备提供商为设备提供驱动软件，通过软件可以与该设备进行通信。
JDBC规范（掌握四个核心对象）：
- DriverManager:用于注册驱动
- Connection: 表示与数据库创建的连接
- Statement: 操作数据库sql语句的对象
- ResultSet: 结果集或一张虚拟表
2 JDBC原理
Java提供访问数据库规范称为JDBC，而生产厂商提供规范的实现类称为驱动。
JDBC是接口，驱动是接口的实现，没有驱动将无法完成数据库连接，从而不能操作数据库！每个数据库厂商都需要提供自己的驱动，用来连接自己公司的数据库，也就是说驱动一般都由数据库生成厂商提供。
3.JDBC
#创建数据库
create database day04;
#使用数据库
use day04;
#创建分类表
create table category(
cid int PRIMARY KEY AUTO_INCREMENT ,
cname varchar(100)
);
#初始化数据
insert into category (cname) values('家电');
insert into category (cname) values('服饰');
insert into category (cname) values('化妆品');
导入驱动jar包
创建lib目录，存放mysql的驱动mysql-connector-java-5.1.37-bin.jar
选中mysql的jar包，右键选择“ Add as Library…” 完成jar导入
1.注册驱动
告知java连接什么数据库
2.获取连接
获取与数据库连接的对象
static Connection getConnection(String url, String user, String password) :试图建立到给定数据库 URL 的连接
3.获取执行sql语句对象
获取一个可以执行sql语句的对象
Statement createStatement(); //创建操作sql语句的对象
4.执行sql语句
增删改
查
5.处理结果
增删改一般不需要处理结果
查询需要处理结果
6.关闭资源
所有关闭资源的方法都是close
案例实现
public static void main(String[] args) throws SQLException, ClassNotFoundException {
/*
注册驱动
java.sql.DriverManager
静态方法
static void registerDriver(Driver driver) 注册驱动
方法参数
java.sql.Driver 接口 mysql驱动一定会实现这个接口
com.mysql.jdbc.Driver
*/
//注册两次
// DriverManager.registerDriver(new Driver());
//直接创建Driver对象 硬编码 写死了
//new Driver();
//反射获取字节码文件对象时 这个类会加载 静态代码块会执行
//这样只加载一次 并且字符串 可以随时修改
Class.forName("com.mysql.jdbc.Driver");
/*
获取连接
java.sql.DriverManager
static Connection getConnection(String url, String user, String password)
String url:数据库连接 jdbc:mysql://IP:3306/数据库名
String user:用户名
String password:密码
*/
String url = "jdbc:mysql://127.0.0.1:3306/day03";
String username = "root";
String password = "root";
Connection con = DriverManager.getConnection(url, username, password);
System.out.println(con);
/*
3.获取执行sql语句对象
java.sql.Connection
Statement createStatement() 创建一个 Statement 对象来将 SQL 语句发送到数据库。
*/
Statement stat = con.createStatement();
/*
4.执行sql语句
java.sql.Statement
方法
int executeUpdate(String sql) 执行增删改的语句
*/
String sql = "DELETE FROM tb_login WHERE uid = '006'";
int row = stat.executeUpdate(sql);
System.out.println(row);
/*
关闭资源
*/
stat.close();
con.close();
}
以上案例中提到
DriverManager.registerDriver(new com.mysql.jdbc.Driver());
不建议使用主要原因为:
- 会导致驱动被注册两次
- 强烈依赖数据库的驱动jar
解决办法:
- Class.forName(“com.mysql.jdbc.Driver”);
java.sql.Statement接口: 操作sql语句，并返回相应结果
常用方法：
-
int executeUpdate(String sql);–执行insert update delete语句. -
ResultSet executeQuery(String sql);--执行select语句. -
boolean execute(String sql);--仅当执行select并且有结果时才返回true，执行其他的语句返回false.
处理结果集
rs.next();//指向第一行
rs.getInt(1)//获取第一行第一列的数据
常用方法：
-
Object getObject(int index)/Object getObject(String name)获得任意对象 -
String getString(int index)/String getString(String name)获得字符串 -
int getInt(int index)/int getInt(String name)获得整形 -
double getDouble(int index)/double getDouble(String name)获得双精度浮点型
关闭资源
rs.close();
stmt.close();
con.close();
三、PreparedStatement
1.SQL注入问题
SQL注入：用户输入的内容作为了SQL语句语法的一部分，改变了原有SQL真正的意义。
假设有登录案例SQL语句如下:
SELECT * FROM 用户表 WHERE NAME = 用户输入的用户名 AND PASSWORD = 用户输的密码;
此时，当用户输入正确的账号与密码后，查询到了信息则让用户登录。但是当用户输入的账号为XXX 密码为：XXX’ OR ‘a’=’a时，则真正执行的代码变为：
SELECT * FROM 用户表 WHERE NAME = ‘XXX’ AND PASSWORD =’ XXX’ OR ’a’=’a’;
此时，上述查询语句时永远可以查询出结果的。那么用户就直接登录成功了，显然我们不希望看到这样的结果，这便是SQL注入问题。
为此，我们使用PreparedStatement来解决对应的问题。
2.PreparedStatement
preparedStatement：预编译对象，是Statement对象的子类。
特点：
- 性能高
- 会把sql语句先编译
- 能过滤掉用户输入的关键字。
PreparedStatement预处理对象，处理的每条sql语句中所有的实际参数，都必须使用占位符?替换。
String sql = "select * from user where username = ? and password = ?";
具体操作如下
public static void main(String[] args) throws Exception {
Scanner sc = new Scanner(System.in);
System.out.println("请输入用户名:");
String username = sc.nextLine();
System.out.println("请输入密码:");
String password = sc.nextLine();
//获取连接
Connection con = JDBCUtils.getConnection();
//获取执行sql语句对象
String sql = "select * from users where username = ? and password = ?";
PreparedStatement ps = con.prepareStatement(sql);
//为?赋值
ps.setObject(1,username);
ps.setObject(2,password);
//执行sql语句
ResultSet rs = ps.executeQuery();
if(rs.next()){
System.out.println("登陆成功");
}else{
System.out.println("登陆失败");
}
rs.close();
ps.close();
con.close();
}
使用PreparedStatement完成增删改
public static void main(String[] args) throws Exception {
Connection con = JDBCUtils.getConnection();
String sql = "delete from users where uid = ? ";
PreparedStatement ps = con.prepareStatement(sql);
//为?赋值
ps.setObject(1,2);
int row = ps.executeUpdate();
System.out.println(row);
ps.close();
con.close();
}
3.优化
设置配置文件
url=jdbc:mysql://127.0.0.1:3306/day03
username=root
password=root
driverClass=com.mysql.jdbc.Driver
public class JDBCUtils {
private static String url ;
private static String username;
private static String password;
private static String driverClass;
static {
try {
init();
//注册驱动
Class.forName(driverClass);
} catch (Exception e) {
e.printStackTrace();
}
}
public static void init() throws IOException {
Properties p = new Properties();
//当前类.class.getClassLoader.getResourceAsStream(文件名);
InputStream in = JDBCUtils.class.getClassLoader().getResourceAsStream("config.properties");
p.load(in);
in.close();
// 为变量赋值
url = p.getProperty("url");
username = p.getProperty("username");
password = p.getProperty("password");
driverClass =p.getProperty("driverClass");
}
public static Connection getConnection() throws SQLException {
Connection con = DriverManager.getConnection(url, username, password);
return con;
}
public static void close(Connection con, Statement stat ,ResultSet rs){
try {
if(con!=null)
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
try {
if(stat!=null)
stat.close();
} catch (SQLException e) {
e.printStackTrace();
}
try {
if(rs!=null)
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void main(String[] args) {
Connection con = null;
PreparedStatement ps = null;
ResultSet rs = null;
List<User> list = new ArrayList<>();
try{
con = JDBCUtils.getConnection();
ps = con.prepareStatement("select * from users");
rs = ps.executeQuery();
while(rs.next()){
int uid = rs.getInt("uid");
String username = rs.getString("username");
String password = rs.getString("password");
// System.out.println(uid+" "+username+" "+password);
//将查询的数据封装到对象中
User u = new User();
u.setUid(uid);
u.setUsername(username);
u.setPassword(password);
//将封装数据的对象放入到集合中
list.add(u);
}
}catch (SQLException e){
e.printStackTrace();
}finally {
JDBCUtils.close(con,ps,rs);
}
for (User user : list) {
System.out.println(user.getUid() +" "+user.getUsername()+" "+user.getPassword());
}
}
786
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
