JDBC学习

JDBC

1.连接数据库

寻找url

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-MmPLaTRg-1652236245712)(C:%5CUsers%5Ccheater%5CDesktop%5C666.assets%5C1649163064558.png)]

方式一

package com.feng.connection;

import org.junit.Test;

import java.sql.Connection;
import java.sql.Driver;
import java.sql.SQLException;
import java.util.Properties;

/**
 * Created by IntelliJ IDEA.
 *
 * @Author : haifeng
 * @create 2022/4/5 19:32
 */
public class ConnectionTest {

//    方式一
    @Test
    public void testConnection1() throws SQLException {

        Driver driver=new com.mysql.jdbc.Driver();

//        url:http://localhost:8080/.... url为固定，可以直接拿来用
        /*主协议:副协议://地址:端口号/数据库名称*/
        String url="jdbc:mysql://localhost:3306/test";
//        将用户名和密码封装在Properties中
        Properties info=new Properties();
        info.setProperty("user","root");
        info.setProperty("password","root");
        Connection conn=driver.connect(url,info);
        System.out.println(conn);
    }
}

报错：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-pWOSWKIV-1652236245713)(C:%5CUsers%5Ccheater%5CDesktop%5C666.assets%5C1649166369389.png)]

解决方法：修改驱动为cj.jdbc

package com.feng.connection;

import org.junit.Test;

import java.sql.Connection;
import java.sql.Driver;
import java.sql.SQLException;
import java.util.Properties;

/**
 * Created by IntelliJ IDEA.
 *
 * @Author : haifeng
 * @create 2022/4/5 19:32
 */
public class ConnectionTest {

//    方式一
    @Test
    public void testConnection1() throws SQLException {

        Driver driver=new com.mysql.cj.jdbc.Driver();

//        url:http://localhost:8080/.... url为固定，可以直接拿来用
        /*主协议:副协议://地址:端口号/数据库名称*/
        String url="jdbc:mysql://localhost:3306/test?serverTimezone=UTC&useSSL=false";
//        将用户名和密码封装在Properties中
        Properties info=new Properties();
        info.setProperty("user","root");
        info.setProperty("password","root");
        Connection conn=driver.connect(url,info);
        System.out.println(conn);
    }
}

运行结果：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-kCeOj9bj-1652236245714)(C:%5CUsers%5Ccheater%5CDesktop%55.3%5C666.assets%5C1649166603417.png)]

方式二

在方式一基础上获取驱动对象方式做修改，运用反射获取对象，保证了代码尽量少让第三方插件影响。

//        1.获取driver对象
        Class clazz = Class.forName("com.mysql.cj.jdbc.Driver");
        Driver diver=(Driver) clazz.newInstance();
//        2.提供要连接的数据库
        String url="jdbc:mysql://localhost:3306/test?serverTimezone=UTC&userSSL=false";
//        3.提供连接需要的用户名和密码
        Properties info=new Properties();
//        获取连接
        info.setProperty("user","root");
        info.setProperty("password","root");
        Connection connection=diver.connect(url,info);
        System.out.println(connection);

运行成功

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-MnzEzT1h-1652236245714)(C:%5CUsers%5Ccheater%5CDesktop%5C666.assets%5C1649167755887.png)]

方式三

使用系统提供的DriverManager类连接数据库。

//        1.获取driver对象
        Class clazz = Class.forName("com.mysql.cj.jdbc.Driver");
        Driver diver=(Driver) clazz.newInstance();

        String url="jdbc:mysql://localhost:3306/test?serverTimezone=UTC&userSSL=false";
        String user="root";
        String password="root";

        DriverManager.registerDriver(diver);

        Connection connection=DriverManager.getConnection(url,user,password);
        System.out.println(connection);

方式四

在方式三的基础上进行优化。将Diver获取对象，注册步骤减去。

因为在mysql的Diver实现类静态模块中声明了如下操作。

//    方式四
    @Test
    public void testConnection4() throws Exception{
//        1.加载驱动
        Class clazz = Class.forName("com.mysql.cj.jdbc.Driver");
//        Driver diver=(Driver) clazz.newInstance();

        String url="jdbc:mysql://localhost:3306/test?serverTimezone=UTC&userSSL=false";
        String user="root";
        String password="root";

//        DriverManager.registerDriver(diver);

        Connection connection=DriverManager.getConnection(url,user,password);
        System.out.println(connection);

    }

方式五

//    最终方式,好处：1.实现代码、数据分离（解耦）2.如果需要修改配置文件信息需要重新打包
    @Test
    public void testConnection5() throws Exception{

        InputStream is = ConnectionTest.class.getClassLoader().getResourceAsStream("jdbc.properties");

        Properties pros = new Properties();
        pros.load(is);

        String user=pros.getProperty("user");
        String password=pros.getProperty("password");
        String url=pros.getProperty("url");
        String driverClass=pros.getProperty("DriverClass");

        Class.forName(driverClass);

        Connection connection=DriverManager.getConnection(url,user,password);
        System.out.println(connection);
    }

配置文件中：jdbc.properties

url=jdbc:mysql://localhost:3306/test?serverTimezone=UTC&userSSL=false
user=root
password=root
DriverClass=com.mysql.cj.jdbc.Driver

2.读数据库进行增删改操作

InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");

        Properties pros = new Properties();
        pros.load(is);

        String user=pros.getProperty("user");
        String password=pros.getProperty("password");
        String url=pros.getProperty("url");
        String driverClass=pros.getProperty("DriverClass");

        Class.forName(driverClass);

        Connection connection= DriverManager.getConnection(url,user,password);
//        System.out.println(connection);
        String str="insert into customers(name,email,birth)values(?,?,?)";
        PreparedStatement ps = connection.prepareStatement(str);
        ps.setString(1,"哪吒");
        ps.setString(2,"nezha@gmail.com");
//        格式转化
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        java.util.Date date=sdf.parse("1000-01-01");
        ps.setDate(3,new Date(date.getTime()));

//        执行sql
        ps.execute();
//        关闭资源
        if (connection!=null)
        connection.close();
        if (ps!=null)
        ps.close();

书写配置文件：

package com.feng.connection;


import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

/**
 * Created by IntelliJ IDEA.
 *
 * @Author : haifeng
 * @create 2022/4/7 20:54
 */
public class JDBCUtils {
    public static Connection getConnection() throws Exception {
        // 1.读取配置文件中的4个基本信息
        InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");

        Properties pros = new Properties();
        pros.load(is);

        String user = pros.getProperty("user");
        String password = pros.getProperty("password");
        String url = pros.getProperty("url");
        String driverClass = pros.getProperty("driverClass");

        // 2.加载驱动
        Class.forName(driverClass);

        // 3.获取连接
        Connection conn = DriverManager.getConnection(url, user, password);
        return conn;
    }
    public static void closeRescource(Connection conn, Statement ps){
        try {
            if(ps != null)
                ps.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            if(conn != null)
                conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

}

ps != null)
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
try {
if(conn != null)
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}

}