Day3
JDBC_Test.java
package Day3;
import com.mysql.jdbc.Connection;
import com.mysql.jdbc.Driver;
import com.mysql.jdbc.Statement;
import org.junit.Test;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
/**
* @PACKAGE_NAME: xl
* @Author: bit
* @DATE: 2021-03-31
*
* @TIME: 8:25
**/
public class JDBC_Test {
// JDBC: 使用Java进行连接数据库
// 数据库的必要信息
// 静态资源信息 url:统一资源定位符
// 格式： jdbc：数据库的驱动(mysql)://localhost(127.0.0.1):3306/需要连接的数据库&字符编码&时区&SSL
// URL="jdbc:mysql://localhost:3306/day3?useUnicode=true&&characterEncoding=utf-8";
private static String URL="jdbc:mysql://localhost:3306/day3?useUnicode=true&&characterEncoding=utf-8&useSSL=false"; //数据库的连接路径
private static String USER="root";
private static String PASSWORD="123123";
//单元测试工具 java中的注解 @Test
//Junti 4 特点：所建立的单元测试方法 不能有返回值类型void 不能存在参数 只可以进行输出
// 单元测试工具 Java中的注解 @Test
// Junti 4 特点:所建立的单元测试方法 不能有返回值类型void 不存在参数 可以进行输出
@Test
public void test1() throws Exception {
Connection connection = null;
Statement statement = null;
// 1.加载驱动
Class.forName("com.mysql.jdbc.Driver");
// 2.获取连接对象
connection = (Connection) DriverManager.getConnection(URL,USER,PASSWORD);
// 3.获得执行sql的对象
statement = (Statement) connection.createStatement();
// 4.执行sql语句并输出结果
// sql语句执行
String sql = "INSERT INTO test(id, name, age, sex) VALUES (NULL ,'陈某', '20', '女');";
int count = statement.executeUpdate(sql);
System.out.println("你的操作影响了数据库中表的"+count+"行");
// 5.处理结果
// 6.释放资源 关闭连接和执行对象
if (statement != null){
statement.close();
}
if (connection != null){
connection.close();
}
}
// 查询
@Test
public void test2(){
// 结果集 resultSet
// 以表结构进行临时结果的存储，通过jdbc进行数据获取
// 数据行指针, .next()
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
Class.forName("com.mysql.jdbc.Driver");
connection = (Connection) DriverManager.getConnection(URL,USER,PASSWORD);
statement = (Statement) connection.createStatement();
String sql = "select * from test where name = '陈某';";
resultSet = statement.executeQuery(sql);
// 将查询数据输出遍历
while (resultSet.next()){
// 过程中 会出现对应数据显示
int id = resultSet.getInt("id");
String name = resultSet.getString("name");
int age = resultSet.getInt("age");
String sex = resultSet.getString("sex");
System.out.println(id+"\t"+name+"\t"+age+"\t"+sex+"\t");
}
} catch (Exception e) {
e.printStackTrace();
} finally {
if (resultSet!=null){
try {
resultSet.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (statement!=null){
try {
statement.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (connection!=null){
try {
connection.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
@Test
public void test3() throws Exception {
// preparedStaement
Connection connection = null;
PreparedStatement preparedStatement = null;
Class.forName("com.mysql.jdbc.Driver");
connection = (Connection) DriverManager.getConnection(URL,USER,PASSWORD);
String sql = "insert into test(name,age,sex) values(?,?,?);";
preparedStatement = connection.prepareStatement(sql);
// 进行赋值 预编译 先进行编译一次 再进行赋值 进行添加等操作
preparedStatement.setObject(1,"温博海");
preparedStatement.setObject(2,19);
preparedStatement.setObject(3,"女");
int count = preparedStatement.executeUpdate();
System.out.println("你的操作影响了"+count+"行!");
if (preparedStatement!=null){
preparedStatement.close();
}
if (connection!=null){
connection.close();
}
}
@Test
public void test4() throws Exception {
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
String name = "%三";
Class.forName("com.mysql.jdbc.Driver");
connection = (Connection) DriverManager.getConnection(URL,USER,PASSWORD);
String sql = "select * from test where name like ?;";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setObject(1,name);
resultSet = preparedStatement.executeQuery();
// 遍历方式
if (resultSet.next()){
int id = resultSet.getInt(1);
String username = resultSet.getString(2);
int age = resultSet.getInt(3);
String sex = resultSet.getString(4);
System.out.println(id+"\t"+username+"\t"+age+"\t"+sex+"\t");
}
if (resultSet!=null){
resultSet.close();
}
if (preparedStatement!=null){
preparedStatement.close();
}
if (connection!=null){
connection.close();
}
}
}
DBuntil.java
package Day3;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
/**
* @PACKAGE_NAME: xl
* @Author: bit
* @DATE: 2021-03-31
* @TIME: 15:28
**/
// jdbc的封装类
public class DBuntil {
private static String URL="jdbc:mysql://localhost:3306/day3?useUnicode=true&&characterEncoding=utf-8&useSSL=false"; //数据库的连接路径
private static String USER="root";
private static String PASSWORD="123123";
// 加载驱动 静态代码块
static {
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
// 1.获取连接
public static Connection getConnection(){
Connection connection = null;
try {
connection = DriverManager.getConnection(URL,USER,PASSWORD);
} catch (SQLException throwables) {
throwables.printStackTrace();
}
return connection;
}
// 关闭资源
public static void closeAll(Connection connection){
if (connection!=null){
try {
connection.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
