JAVA笔记-JDBC开发
JDBC开发
- 注册驱动
使用Class.forName("com.mysql.jdbc.Driver");手动加载字节码文件到JVM
Class.forName("com.mysql.jdbc.Driver");//加载驱动类
- 链接数据库
- 通过DriverManager.etConnection(url,user,password)获取数据库连接对象
- URL：jdbc:mysql://localhost:3306/database
- username:root
- password:123456
- 获取发送sql的对象
Statement statement = connection.createStatement();//创建对象
- 编写sql语句
int result = statement.executeUpdate("");
- 处理接收结果
- 释放资源
example：
package com.study.day1;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class TestJDBC {
public static void main(String[] args) throws Exception{
//1.注册驱动 加载驱动
Class.forName("com.mysql.jdbc.Driver");
//2.获得连接
String url = "jdbc:mysql://localhost:3306/companydb";
String user="root";
String password = "123456";
Connection connection = DriverManager.getConnection(url,user,password);
if(connection!=null){
System.out.println("succerss");
}else {
System.out.println("error!!!");
}
//3. 获得sql执行语句的对象 statement对象
Statement statement = connection.createStatement();//创建对象
//4. 编写sql语句，执行sql语句
// String sql = "";
//
// int result = statement.executeUpdate(sql);//dml操作调用的方法
int result = statement.executeUpdate("");
System.out.println(result);
//5. 处理接受结果 先开后关
if(result=1){
System.out.println("success");
}else {
System.out.println("error");
}
//释放资源 先开后关
statement.close();
connection.close();
}
}
resultSet结果集
案例：
package com.study.day1;
import com.sun.org.apache.bcel.internal.generic.DREM;
import java.sql.*;
public class QueryJDBC {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
Class.forName("com.mysql.jdbc.Driver");
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost/companydb","root", "123456");
Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery("select * from jdbctest");
while (resultSet.next()){
String job_id = resultSet.getString(1);
String job_password = resultSet.getString(2);
String job_phone = resultSet.getString(3);
String job_address = resultSet.getString(4);
System.out.println(job_id+"\t"+job_password+"\t"+job_phone+"\t"+job_address);
}
resultSet.close();
statement.close();
connection.close();
}
}
package com.study.day1;
import com.sun.org.apache.bcel.internal.generic.DREM;
import java.sql.*;
public class QueryJDBC2 {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
Class.forName("com.mysql.jdbc.Driver");
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost/companydb","root", "123456");
Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery("select * from jdbctest");
while (resultSet.next()){
String job_id = resultSet.getString("username");
String job_password = resultSet.getString("password");
String job_phone = resultSet.getString("phone");
String job_address = resultSet.getString("address");
System.out.println(job_id+"\t"+job_password+"\t"+job_phone+"\t"+job_address);
}
resultSet.close();
statement.close();
connection.close();
}
}
这两个案例的区别在于在便利resultset结果集的时候前者是通过列名，后者是通过表名查询，比较推荐的是后者。
登录jdbc
案例：
package com.study.day1.LoginJdbc;
import java.sql.*;
import java.util.Scanner;
public class LoginJDBC {
public static void main(String[] args) throws Exception {
Scanner scanner = new Scanner(System.in);
System.out.println("请输入用户名：");
String username = scanner.next();
System.out.println("请输入密码：");
String password = scanner.next();
Class.forName("com.mysql.jdbc.Driver");
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/companydb", "root", "123456");
Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery("select * from jdbctest where username = '"+username+"' and password = '"+password+"'");
if (resultSet.next()){
System.out.println("登录成功！");
}else {
System.out.println("登陆失败");
}
resultSet.close();
statement.close();
connection.close();
}
}
登录是造成sql注入的问题
scanner.next()与scanner.nextLine()的区别：
next：不能读取空格 相当于结束
nextLine: 把空格当作字符，可以读取空格
当程序使用scanner.nextLine()时会造成以下情况：
在输入用户名时输入了恶意的sql语句，or判定成永真式可以得到正确结果。
避免sql注入
由于编写的sql语句是在用户输入数据，整合后在进行编译的，所以我们要在用户输入数据前进行编译好完整的sql语句在进行填充查询即可(预编译！！)
PreparedStatement：继承statement接口，预编译使用的方法
- 参数标记
PreparedStatement pstmt = conn.preparedstatement("select * from user where username=? and password = ?");
在JDBC中，所有参数都是由?符号占位，即为占位符。
- 动态参数绑定
在完成sql预编译语句后，我们需要对预编译占位符进行动态参数绑定
preparedStatement.setString(1,username);
preparedStatement.setString(2.password);
package com.study.day1.LoginJdbc;
import java.sql.*;
import java.util.Scanner;
public class LoginJDBC {
public static void main(String[] args) throws Exception {
Scanner scanner = new Scanner(System.in);
System.out.println("请输入用户名：");
String username = scanner.nextLine();
System.out.println("请输入密码：");
String password = scanner.nextLine();
Class.forName("com.mysql.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/companydb", "root", "123456");
PreparedStatement pstmt = conn.prepareStatement("select * from jdbctest where username=? and password = ?");
pstmt.setString(1,username);
pstmt.setString(2,password);
System.out.println(pstmt);
}
}
DBUtils(封装工具类)
对于jdbc而言，模板就是这个样子
package com.study.day1.LoginJdbc2;
import java.sql.*;
import java.util.Scanner;
public class LoginJDBC2 {
public static void main(String[] args) throws Exception {
Scanner scanner = new Scanner(System.in);
System.out.println("请输入用户名:");
String username = scanner.nextLine();
System.out.println("请输入密码：");
String password = scanner.nextLine();
//1. 加载驱动类 Class.forName()
Class.forName("com.mysql.jdbc.Driver");
//2. 获取数据库对象 通过DriverManger加载getConnection
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/companydb", "root","123456");
//3. 预编译加载statedment子类preparedStatement
PreparedStatement preparedStatement = connection.prepareStatement("select * from jdbctest where username=? and password = ?");
//占位符
preparedStatement.setString(1,username);
preparedStatement.setString(2.password);
//设置resultset对象接收数据集
ResultSet resultSet = preparedStatement.executeQuery();
//4.处理结果
if (resultSet.next()){
System.out.println("登录成功");
}else {
System.out.println("登录失败！！！");
}
}
}
之所以封装工具类，是因为有很多代码可以重复利用，我们只需要调用这个类就可以减少很多代码量。
对于jdbc来说，加载驱动类，获取数据库对象，释放资源都是重复的代码，我们可以把它们封装到一个java模块中随时调用。
DBUtils.java
package com.study.day1.LoginJdbc2;
import java.sql.*;
/*
1. 获取链接方法
2. 释放资源方法
*/
//1. 获取连接，用getConnection命名，其中可能会产生异常的地方try_catch以下
public class DBUtils {
public static Connection getConnection(){
Connection connection = null;
try {
Class.forName("com.mysql.jdbc.Driver");
connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/companydb","root","123456");
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
return connection;
//释放资源， 用closeAll命名
}
public static void closeAll(Connection connection, Statement statement, ResultSet resultSet){
try {
if (connection!=null){
connection.close();
}
if (statement!=null){
statement.close();
}
if (resultSet!=null){
resultSet.close();
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
DBUtils写好后，在上述LoginJDBC2.java中可以将部分删除换成调用函数。
package com.study.day1.LoginJdbc2;
import java.sql.*;
import java.util.Scanner;
public class LoginJDBC2 {
public static void main(String[] args) throws Exception {
Scanner scanner = new Scanner(System.in);
System.out.println("请输入用户名:");
String username = scanner.nextLine();
System.out.println("请输入密码：");
String password = scanner.nextLine();
Connection connection = DBUtils.getConnection();
//3. 预编译加载statedment子类preparedStatement
PreparedStatement preparedStatement = connection.prepareStatement("select * from jdbctest where username=? and password = ?");
//占位符
preparedStatement.setString(1,username);
preparedStatement.setString(2,password);
//设置resultset对象接收数据集
ResultSet resultSet = preparedStatement.executeQuery();
//4.处理结果
if (resultSet.next()){
System.out.println("success");
}else{
System.out.println("error!");
}
//释放资源
DBUtils.closeAll(connection,preparedStatement,resultSet);
}
}
跨平台方案(可以更便捷操作)
定义public static finall Properties prop = new Properties();//读取配置文件的Map
定义 static{
//首次使用工具类时，需要加载驱动类
InputStream is = JDBCUtil.class.getResourceAsStream("路径");//通过服用本类自带的流，读取jdbc.properties配置文件
prop.load(is);
String driverName = prop.getProperty("driver");//通过driverName的键获取对应的值
Class.forName(driverName);//加载驱动
}
DBUtils.java
package com.study.day1.LoginJdbc3;
import com.study.day1.LoginJdbc2.DBUtils;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
public class DBUtils_prop {
//1.加载静态资源
private static final Properties PROPERTIES = new Properties(); //给一个私有的静态的常量->存储配置文件的map集合
static {
//2. 拿java里面一个字节输入流is，不用new，可以用自带的
InputStream is = DBUtils.class.getResourceAsStream("/DB.properies");
//3. 通过properties中的一个方法load()把文件里的东西读取进来
try {
PROPERTIES.load(is);//通过流将文件内容加载到properties集合
Class.forName(PROPERTIES.getProperty("driver"));
} catch (IOException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
public static Connection getConnection(){
try {
Connection connection = null;
connection = DriverManager.getConnection(PROPERTIES.getProperty("url"),PROPERTIES.getProperty("username"),PROPERTIES.getProperty("password"));
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
public static void closeAll(Connection connection, Statement statement, ResultSet resultSet){
try {
if (resultSet!=null){
resultSet.close();
}
if (statement!=null){
statement.close();
}
if (connection!=null){
connection.close();
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
loginJDBC3.java
package com.study.day1.LoginJdbc3;
import com.study.day1.LoginJdbc2.DBUtils;
import java.sql.*;
import java.util.Scanner;
public class loginJDBC3 {
public static void main(String[] args) throws Exception {
Scanner scanner = new Scanner(System.in);
System.out.println("请输入用户名:");
String username = scanner.nextLine();
System.out.println("请输入密码：");
String password = scanner.nextLine();
Connection connection = DBUtils.getConnection();
//3. 预编译加载statedment子类preparedStatement
PreparedStatement preparedStatement = connection.prepareStatement("select * from jdbctest where username=? and password = ?");
//占位符
preparedStatement.setString(1,username);
preparedStatement.setString(2,password);
//设置resultset对象接收数据集
ResultSet resultSet = preparedStatement.executeQuery();
//4.处理结果
if (resultSet.next()){
System.out.println("success");
}else{
System.out.println("error!");
}
//释放资源
DBUtils.closeAll(connection,preparedStatement,resultSet);
}
}
DB.properties
