JDBC:
概念:JDBC(Java Database Connectivity)。但它只是规范,不做具体实现。于是数据库厂商又根据JDBC标准,实现自家的驱动Driver。如:mysql驱动com.mysql.cj.jdbc.Driver,Oracle的驱动oracle.jdbc.OracleDriver。有了这套解决方案,java就可以访问数据库中的数据了。
使用步骤
1.找到jar包,并r
2.利用工具类,通过java连接数据库(用户名、密码、数据库名、数据库端口号)
3.通过java程序,发起sql语句
4.通过程序处理结果
package cn.tedu.jdbc;
import java.sql.*;
import java.util.Arrays;
public class Test1 {
public static void main(String[] args) throws Exception {
//1.注册驱动
Class.forName("com.mysql.cj.jdbc.Driver");
//2.获取数据库的连接
//String url="协议://数据库的服务器的ip地址:端口号/数据库名";
String url="jdbc:mysql://localhost:3306/cgb2112";
Connection c=DriverManager.getConnection(url,"root","root");
//3.获取传输器
Statement s = c.createStatement();
//4.执行sql--查询部门表的所有数据
ResultSet r = s.executeQuery("select * from dept");
System.out.println("连接成功");
//5.处理结果
while (r.next()){
int deptno=r.getInt(1);
String s1 = r.getString(2);
String s2 = r.getString(3);
System.out.print(deptno+"\t");
System.out.print(s1+"\t");
System.out.println(s2);
}
//6.关闭资源
r.close();
s.close();
c.close();
}
}
SQL攻击:
***添加数据时的格式:'"+s1+"'(单引号 双引号 加号 内容 加号 双引号 单引号)
当用户输入特殊符号('#)时,不需要密码登录
产生原因:#在sql中表示注释,相当于后面语句被注释
package cn.tedu.jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Scanner;
public class Test5 {
public static void main(String[] args) throws Exception {
System.out.println("请输入用户名:");
String s = new Scanner(System.in).nextLine();
System.out.println("请输入密码:");
String s1 = new Scanner(System.in).nextLine();
Class.forName("com.mysql.cj.jdbc.Driver");
String url="jdbc:mysql://localhost:3306/cgb2112?characterEncoding=utf8";
Connection c = DriverManager.getConnection(url, "root", "root");
Statement d = c.createStatement();
String sql="select * from user where name='"+s+"' and pwd='"+s1+"'";
ResultSet r = d.executeQuery(sql);
if(r.next()){
System.out.println("登录成功");
}else{
System.out.println("登录失败");
}
}
}
执行结果:
请输入用户名:
王子豪'#
请输入密码:
登录成功
处理方法:使用新的传输器prepareStatement
package cn.tedu.jdbc;
import java.sql.*;
import java.util.Scanner;
public class Test5 {
public static void main(String[] args) throws Exception {
System.out.println("请输入用户名:");
String s = new Scanner(System.in).nextLine();
System.out.println("请输入密码:");
String s1 = new Scanner(System.in).nextLine();
Class.forName("com.mysql.cj.jdbc.Driver");
String url="jdbc:mysql://localhost:3306/cgb2112?characterEncoding=utf8";
Connection c = DriverManager.getConnection(url, "root", "root");
//Statement d = c.createStatement();
String sql="select * from user where name=? and pwd=?";
//新的传输器
PreparedStatement p = c.prepareStatement(sql);
//给Sql绑定参数,给第一个问号设置s的值,给第二个问号设置s1的值
p.setString(1,s);
p.setString(2,s1);
ResultSet r = p.executeQuery();
if(r.next()){
System.out.println("登录成功");
}else{
System.out.println("登录失败");
}
}
}
执行结果:
请输入用户名:
王子豪'#
请输入密码:
登录失败
封装:用于代码去重
package cn.tedu.jdbc;
import java.sql.*;
public class Until {
public static Connection get() throws Exception {
Class.forName("com.mysql.cj.jdbc.Driver");
String url="jdbc:mysql://localhost:3306/cgb2112?characterEncoding=utf8";
Connection c = DriverManager.getConnection(url,"root","root");
return c;
}
public static void close(Connection c, ResultSet r, PreparedStatement p){
{
if(r!=null){
try {
r.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if(p!=null){
try {
p.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if(c!=null){
try {
c.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
}
package cn.tedu.jdbc;
import java.sql.*;
import java.util.Scanner;
public class Test8 {
public static void main(String[] args) {
Connection c=null;
ResultSet r=null;
PreparedStatement p=null;
try {
c = Until.get();
String sql="select * from dept where deptno=?";
p= c.prepareStatement(sql);
System.out.println("请输入id:");
int i = new Scanner(System.in).nextInt();
p.setObject(1,i);
r = p.executeQuery();
while (r.next()){
Object o1 = r.getObject(1);
Object o2 = r.getObject(2);
Object o3 = r.getObject(3);
System.out.print(o1+"\t");
System.out.print(o2+"\t");
System.out.println(o3+"\t");
System.out.println("查找成功");
}
}catch (Exception e){
System.out.println("查找失败");
}finally {
Until.close(c,r,p);
}
}
}
DBCP
- DataBaseConnectionPool: 数据库连接池
- 作用: 可以将数据库连接重用,避免频繁开关连接导致的资源浪费
如何使用数据库连接池?
1.从苍老师文档服务器中找到连接池的依赖,导入pom.xml中
2.代码:
package cn.tedu;
import com.alibaba.druid.pool.DruidDataSource;
import java.sql.Connection;
import java.sql.SQLException;
public class Demo01 {
public static void main(String[] args) throws SQLException {
//创建数据库连接池
DruidDataSource dds=new DruidDataSource();
//设置数据库连接信息
dds.setUrl("jdbc:mysql://localhost:3306/empdb?characterEncoding=utf8");
dds.setUsername("root");
dds.setPassword("root");
//设置初始连接数量
dds.setInitialSize(3);
//设置最大连接数量
dds.setMaxActive(5);
//从连接池对象中获取连接
Connection conn=dds.getConnection();
System.out.println("连接"+conn);
}
}
代码封装
package cn.tedu;
import com.alibaba.druid.pool.DruidDataSource;
import java.sql.Connection;
import java.sql.SQLException;
public class DButils {
private static DruidDataSource dds;
static{
//创建数据库连接池
dds=new DruidDataSource();
//设置数据库连接信息
dds.setUrl("jdbc:mysql://localhost:3306/empdb?characterEncoding=utf8");
dds.setUsername("root");
dds.setPassword("root");
dds.setInitialSize(3);//设置初始连接数量
dds.setMaxActive(5); //设置最大连接数量
}
public static Connection getConn() throws SQLException {
//从连接池对象中获取连接
Connection conn=dds.getConnection();
System.out.println("连接"+conn);
return conn;
}
}
查找功能的实现
package cn.tedu;
import java.sql.*;
public class Demo02 {
public static void main(String[] args) {
try (Connection conn = DButils.getConn()){
Statement s = conn.createStatement();
ResultSet rs = s.executeQuery("select name from emp");
while(rs.next()){
String s1 = rs.getString(1);
System.out.println(s1);
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
注册功能实现
package cn.tedu;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;
//注册
public class Demo03 {
public static void main(String[] args) {
Scanner sc = new Scanner(System.in);
System.out.println("请输入用户名");
String username = sc.nextLine();
System.out.println("请输入密码");
String password = sc.nextLine();
try(Connection conn = DButils.getConn();) {
Statement s = conn.createStatement();
s.executeUpdate("insert into user values(null,'" + username + "','" + password + "',null)");
System.out.println("注册完成");
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
登录功能实现
package cn.tedu;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;
//登录
public class Demo04 {
public static void main(String[] args) {
Scanner sc = new Scanner(System.in);
System.out.println("请输入用户名");
String username = sc.nextLine();
System.out.println("请输入密码");
String password = sc.nextLine();
try(Connection conn = DButils.getConn()) {
Statement s = conn.createStatement();
ResultSet re = s.executeQuery("select count(*) from user where username='" + username + "' and password='" + password + "'");
//结果集对象游标默认没有指向任何一条数据
re.next();
int count = re.getInt(1);
if(count>0){
System.out.println("登录成功");
}else{
System.out.println("登陆失败");
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}