文章目录
来源于how2j
建立连接
导入Mysql-jdbc的jar包
初始化驱动
- 通过Class.forName(“com.mysql.jdbc.Driver”);
- 初始化驱动类com.mysql.jdbc.Driver;就在 mysql-connector-java-5.0.8-bin.jar中
- 如果忘记了第一个步骤的导包,就会抛出ClassNotFoundException
- Class.forName是把这个类加载到JVM中,加载的时候,就会执行其中的静态初始化块,完成驱动的初始化的相关工作。
- 代码:
package jdbc;
public class TestJDBC {
public static void main(String[] args) {
//初始化驱动
try {
//驱动类com.mysql.jdbc.Driver
//就在 mysql-connector-java-5.0.8-bin.jar中
//如果忘记了第一个步骤的导包,就会抛出ClassNotFoundException
Class.forName("com.mysql.jdbc.Driver");
System.out.println("数据库驱动加载成功 !");
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
- res:
建立与数据库的连接
- 建立与数据库的Connection连接
- 这里需要提供:
数据库所处于的ip:127.0.0.1 (本机)
数据库的端口号: 3306 (mysql专用端口号)
数据库名称 how2java
编码方式 UTF-8
账号 root
密码 admin
- 代码:
package jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class TestJDBC {
public static void main(String[] args) {
try {
Class.forName("com.mysql.jdbc.Driver");
// 建立与数据库的Connection连接
// 这里需要提供:
// 数据库所处于的ip:127.0.0.1 (本机)
// 数据库的端口号: 3306 (mysql专用端口号)
// 数据库名称 how2java
// 编码方式 UTF-8
// 账号 root
// 密码 admin
Connection c = DriverManager
.getConnection(
"jdbc:mysql://127.0.0.1:3306/how2java?characterEncoding=UTF-8",
"root", "admin");
System.out.println("连接成功,获取连接对象: " + c);
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
- res:
创建Statement
- Statement是用于执行SQL语句的,比如增加,删除
- 代码:
package jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class TestJDBC {
public static void main(String[] args) {
try {
Class.forName("com.mysql.jdbc.Driver");
Connection c = DriverManager
.getConnection(
"jdbc:mysql://127.0.0.1:3306/how2java?characterEncoding=UTF-8",
"root", "admin");
// 注意:使用的是 java.sql.Statement
// 不要不小心使用到: com.mysql.jdbc.Statement;
Statement s = c.createStatement();
System.out.println("获取 Statement对象: " + s);
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
- res:
执行SQL语句
-s.execute
执行sql语句;执行成功后,用mysql-front进行查看,明确插入成功
- 代码:
package jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class TestJDBC {
public static void main(String[] args) {
try {
Class.forName("com.mysql.jdbc.Driver");
Connection c = DriverManager
.getConnection(
"jdbc:mysql://127.0.0.1:3306/how2java?characterEncoding=UTF-8",
"root", "admin");
Statement s = c.createStatement();
// 准备sql语句
// 注意: 字符串要用单引号'
String sql = "insert into hero values(null,"+"'提莫'"+","+313.0f+","+50+")";
s.execute(sql);
System.out.println("执行插入语句成功");
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
关闭连接
- 数据库的连接是有限资源,相关操作结束后,养成关闭数据库的好习惯
- 先关闭Statement
- 后关闭Connection
package jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class TestJDBC {
public static void main(String[] args) {
Connection c = null;
Statement s = null;
try {
Class.forName("com.mysql.jdbc.Driver");
c = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/how2java?characterEncoding=UTF-8", "root",
"admin");
s = c.createStatement();
String sql = "insert into hero values(null," + "'提莫'" + "," + 313.0f + "," + 50 + ")";
s.execute(sql);
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
// 数据库的连接时有限资源,相关操作结束后,养成关闭数据库的好习惯
// 先关闭Statement
if (s != null)
try {
s.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
// 后关闭Connection
if (c != null)
try {
c.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
JDBC的增删改
增
public static void main(String[] args) {
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
try (
Connection c = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/how2java?characterEncoding=UTF-8",
"root", "admin");
Statement s = c.createStatement();
)
{
String sql = "insert into hero values(null," + "'提莫'" + "," + 313.0f + "," + 50 + ")";
s.execute(sql);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
删除
public static void main(String[] args) {
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
try (
Connection c = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/how2java?characterEncoding=UTF-8",
"root", "admin");
Statement s = c.createStatement();
)
{
String sql = "delete from hero where id = 5";
s.execute(sql);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
修改
public static void main(String[] args) {
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
try (
Connection c = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/how2java?characterEncoding=UTF-8",
"root", "admin");
Statement s = c.createStatement();
)
{
String sql = "update hero set name = 'name 5' where id = 3";
s.execute(sql);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
通用方法
- 设计一个方法:
public static void execute(String sql)
:方法接受的参数是SQL语句,无论是增加,删除,还是修改,都调用这个方法,每次传不同的SQL语句作为参数 - 代码:
public static void execule(String sql)
{
Connection c = null;
Statement s = null;
try
{
Class.forName("com.mysql.jdbc.Driver");
c = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/how2java?characterEncoding=UTF-8","root","admin");
s = c.createStatement();
s.execute(sql);
System.out.println("操作成功!");
}
catch (ClassNotFoundException e)
{
e.printStackTrace();
}
catch (SQLException e)
{
e.printStackTrace();
}
finally
{
if(s != null)
{
try
{
s.close();
}
catch(SQLException e)
{
e.printStackTrace();
}
}
if(c != null)
{
try
{
c.close();
}
catch(SQLException e)
{
e.printStackTrace();
}
}
}
}
查询
利用ResultSet接收返回值
- 代码:
package jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestJDBC {
public static void main(String[] args) {
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
try (Connection c = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/how2java?characterEncoding=UTF-8",
"root", "admin"); Statement s = c.createStatement();) {
String sql = "select * from hero";
// 执行查询语句,并把结果集返回给ResultSet
ResultSet rs = s.executeQuery(sql);
while (rs.next()) {
int id = rs.getInt("id");// 可以使用字段名
String name = rs.getString(2);// 也可以使用字段的顺序
float hp = rs.getFloat("hp");
int damage = rs.getInt(4);
System.out.printf("%d\t%s\t%f\t%d%n", id, name, hp, damage);
}
// 不一定要在这里关闭ReultSet,因为Statement关闭的时候,会自动关闭ResultSet
// rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
- res:
SQL语句判断账号密码是否正确
-
判断账号密码的正确方式是根据账号和密码到表中去找数据,如果有数据,就表明密码正确了,如果没数据,就表明密码错误。
-
不恰当的方式 是把uers表的数据全部查到内存中,挨个进行比较。 如果users表里有100万条数据呢? 内存都不够用的。
-
代码:
try (Connection c = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/how2java?characterEncoding=UTF-8",
"root", "admin");
Statement s = c.createStatement();
) {
String name = "dashen";
//正确的密码是:thisispassword
String password = "thisispassword1";
String sql = "select * from user where name = '" + name +"' and password = '" + password+"'";
// 执行查询语句,并把结果集返回给ResultSet
ResultSet rs = s.executeQuery(sql);
if(rs.next())
System.out.println("账号密码正确");
else
System.out.println("账号密码错误");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
- 即sql找到了结果 ResultSet里才有next对象
- res:
找到总数
- 设计一个方法,进行分页查询:
public static void list(int start, int count)
:start 表示开始页数,count表示一页显示的总数;list(0,5) 表示第一页,一共显示5条数据;list(10,5) 表示第三页,一共显示5条数据 - 代码:
String sql = "select count(*) from hero";
ResultSet rs = s.executeQuery(sql);
int total = 0;
while (rs.next()) {
total = rs.getInt(1);//字段顺序
}
分页查询
- 代码:
package jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestJDBC {
public static void main(String[] args) {
list(0, 5);
System.out.println();
list(5, 10);
}
public static void list(int start, int count) {
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
// TODO 自动生成的 catch 块
e.printStackTrace();
}
Connection c = null;
try {
c = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/how2java?characterEncoding=UTF-8"
,"root","admin");
Statement s = c.createStatement();
String str = "select * from hero limit "+start+","+count;
ResultSet rs = s.executeQuery(str);
while(rs.next()) {
int id = rs.getInt(1);
String name = rs.getString(2);
float hp = rs.getFloat(3);
int damage = rs.getInt(4);
System.out.printf("%d \t %s \t %f \t %d %n",id,name,hp,damage);
}
} catch (SQLException e) {
// TODO 自动生成的 catch 块
e.printStackTrace();
}
}
}
- res:
PreparedStatement
PreparedStatement的使用
- 和 Statement一样,PreparedStatement也是用来执行sql语句的
- 与创建Statement不同的是,需要根据sql语句创建PreparedStatement
- 除此之外,还能够通过设置参数,指定相应的值,而不是Statement那样使用字符串拼接
- 代码:
package jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class TestJDBC {
public static void main(String[] args) {
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
String sql = "insert into hero values(null,?,?,?)";
try (Connection c = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/how2java?characterEncoding=UTF-8","root", "admin");
// 根据sql语句创建PreparedStatement
PreparedStatement ps = c.prepareStatement(sql);
) {
// 设置参数
ps.setString(1, "提莫");
ps.setFloat(2, 313.0f);
ps.setInt(3, 50);
// 执行
ps.execute();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
- res:
PreparedStatement的优点
- Statement 需要进行字符串拼接,可读性和维护性比较差
String sql = "insert into hero values(null,"+"'提莫'"+","+313.0f+","+50+")";
- PreparedStatement 使用参数设置,可读性好,不易犯错
String sql = "insert into hero values(null,?,?,?)";
String sql = "insert into hero values(null,?,?,?)";
PreparedStatement ps = c.prepareStatement(sql);
// PreparedStatement 使用参数设置,可读性好,不易犯错
// "insert into hero values(null,?,?,?)";
ps.setString(1, "提莫");
ps.setFloat(2, 313.0f);
ps.setInt(3, 50);
ps.execute();
- PreparedStatement有预编译机制,性能比Statement更快
// PreparedStatement 执行10次,只需要1次把SQL语句传输到数据库端
// 数据库对带?的SQL进行预编译
// 每次执行,只需要传输参数到数据库端
// 1. 网络传输量比Statement更小
// 2. 数据库不需要再进行编译,响应更快
- PreparedStatement的优点3-防止SQL注入式攻击:
- 代码:
public static void main(String[] args) {
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
String sql = "select * from hero where name = ?";
try (Connection c = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/how2java?characterEncoding=UTF-8","root", "admin");
Statement s = c.createStatement();
PreparedStatement ps = c.prepareStatement(sql);
) {
// 假设name是用户提交来的数据,可以自己编辑,如果恶意编辑为以下
String name = "'盖伦' OR 1=1";
String sql0 = "select * from hero where name = " + name;
// 拼接出来的SQL语句就是
// select * from hero where name = '盖伦' OR 1=1
// 因为有OR 1=1,所以恒成立
// 那么就会把所有的英雄都查出来,而不只是盖伦
// 如果Hero表里的数据是海量的,比如几百万条,把这个表里的数据全部查出来
// 会让数据库负载变高,CPU100%,内存消耗光,响应变得极其缓慢
System.out.println(sql0);
ResultSet rs0 = s.executeQuery(sql0);
while (rs0.next()) {
String heroName = rs0.getString("name");
System.out.println(heroName);
}
s.execute(sql0);
// 使用预编译Statement就可以杜绝SQL注入
ps.setString(1, name);
ResultSet rs = ps.executeQuery();
// 查不出数据出来
while (rs.next()) {
String heroName = rs.getString("name");
System.out.println(heroName);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
execute与executeUpdate
- execute与executeUpdate的相同点:都可以执行增加,删除,修改
- 不同1:execute可以执行查询语句,然后通过getResultSet,把结果集取出来;executeUpdate不能执行查询语句
- 不同2:execute返回boolean类型,true表示执行的是查询语句,false表示执行的是insert,delete,update等等
executeUpdate返回的是int,表示有多少条数据受到了影响