Java连接数据库

JDBC

数据库驱动和JDBC

(1)数据库驱动是连接数据库所需，由厂商提供，程序和数据库驱动打交道
(2)jdbc是一种规范，主要是为了简化开发人员对数据的统一的操作而提供的一个规范

第一个JDBC程序

package com.ch.jdbc;

import java.sql.*;

public class MyFirstJdbc {

    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //1、加载数据库驱动
        Class.forName("com.mysql.jdbc.Driver");

        //2、准备连接信息：url，username，password
        String url = "jdbc:mysql://localhost:3306/mysqlstudy?serverTimezone=GMT&characterEncoding=utf8&useSSL=true";
        String username = "root";
        String password = "root";

        //3、连接数据库
        Connection conn = DriverManager.getConnection(url, username, password);

        //4、获取执行SQL语句的对象
        Statement statement = conn.createStatement();

        //5、执行SQL语句
        ResultSet query = statement.executeQuery("select * from student");
        while (query.next()){
            int id = query.getInt("id");
            int score = query.getInt("score");
            String name = query.getString("name");
            System.out.println(id + "------"+ name + "------"+score);
        }

        //6、释放资源
        query.close();
        statement.close();
        conn.close();
    }

}

URL

String url = "jdbc:mysql://localhost:3306/mysqlstudy?serverTimezone=GMT&characterEncoding=utf8&useSSL=true";

//mysql -- 3306
//jdbc:mysql://localhost:3306/数据库?参数1&参数2&参数3

//oracle -- 1521
//jdbc:oracle:thin@localhost:1521:sid

//SQLServer -- 1433
//jdbc:sqlserver://localhost:1433;DatabaseName=test

DriverManager

Connection conn = DriverManager.getConnection(url, username, password);

//conn代表数据库对象
//数据库可以设置提交
//设置回滚
//设置自动提交
conn.commit();
conn.rollback();
conn.setAutoCommit();

Statement

Statement statement = conn.createStatement();

//statement用来执行SQL的对象，prepareStatement也是用来执行SQL对象的
statement.execute();        //执行任何SQL
statement.executeQuery();   //查询操作，返回ResultSet
statement.executeUpdate();  //更新、插入、删除。返回受影响行
statement.executeBatch();   //执行多个SQL

ResultSet

//封装了所有的查询结果

query.getObject();  //不知道数据类型的情况下使用
query.getString();
query.getInt();
query.getDate();
……

简单封装代码实现

jdbc.properties

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/mysqlstudy?serverTimezone=GMT&characterEncoding=utf8&useSSL=true
username=root
password=root

JdbcUtil

package com.ch.util;

import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

public class JdbcUtil {

    private static String driver;
    private static String url;
    private static String username;
    private static String password;

    //1、获取配置文件的信息并加载驱动
    static {
        try {
            //读取配置文件的内容
            InputStream in = JdbcUtil.class.getClassLoader().getResourceAsStream("jdbc.properties");
            Properties properties = new Properties();
            properties.load(in);

            //获取数据库相关信息
            driver = properties.getProperty("driver");
            url = properties.getProperty("url");
            username = properties.getProperty("username");
            password = properties.getProperty("password");

            //加载驱动
            Class.forName(driver);
        } catch (Exception e) {
        }
    }

    //2、获取连接
    public static Connection getConnection(){
        Connection conn = null;
        try {
            conn = DriverManager.getConnection(url,username,password);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }

    //3、释放资源
    public static void close(Connection conn, Statement st, ResultSet rs){
        if (rs != null){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (st != null){
            try {
                st.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (conn != null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

}

JdbcTest

package com.ch.jdbc;

import com.ch.util.JdbcUtil;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * 增删查改测试
 */
public class JdbcTest {

    /**
     * 增删改操作
     * @param sql   填写需要执行的sql语句
     * @param message   操作是增加还是删除或者修改
     */
    public static void update(String sql,String message){
        //获取连接
        Connection conn = JdbcUtil.getConnection();
        //获取执行SQL的对象
        Statement st = null;
        int result = 0;
        try {
            st = conn.createStatement();
            //执行SQL
            result = st.executeUpdate(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        if (result > 0){
            System.out.println(message+"成功");
        }else{
            System.out.println(message+"失败");
        }
        //执行完释放资源
        JdbcUtil.close(conn,st,null);
    }

    /**
     * 查询操作
     */
    public static void query(){
        //获取连接
        Connection conn = JdbcUtil.getConnection();
        //获取执行SQL的对象
        Statement st = null;
        ResultSet rs = null;
        try {
            st = conn.createStatement();
            //执行SQL语句
            String sql = "select * from student";
            rs = st.executeQuery(sql);
            while (rs.next()){
                int id = rs.getInt("id");
                int score = rs.getInt("score");
                String name = rs.getString("name");
                System.out.println(id + "------"+ name + "------"+score);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        //执行完释放资源
        JdbcUtil.close(conn,st,null);
    }

    //测试
    public static void main(String[] args) throws SQLException {
        //添加
        String sql = "insert into student(id,score,`name`) values(10,72,'小四');";
        String message = "插入";
        update(sql,message);

        //修改
        String sql1 = "update student set `name`='张华' where id = 10";
        String message1 = "修改";
        update(sql1,message1);

        //删除
        String sql2 = "delete from student where id = 10";
        String message2 = "删除";
        update(sql2,message2);

        //查询
        query();
    }

}

SQL注入问题

SQL注入就是sql存在漏洞，SQL语句被拼接

public static void doLogin(String uname){
    //获取连接
    Connection conn = JdbcUtil.getConnection();
    //获取执行SQL的对象
    Statement st = null;
    ResultSet rs = null;
    try {
        st = conn.createStatement();
        //执行SQL语句
        String sql = "select * from student where `name`='"+uname+"'";
        rs = st.executeQuery(sql);
        while (rs.next()){
            int id = rs.getInt("id");
            int score = rs.getInt("score");
            String name = rs.getString("name");
            System.out.println(id + "------"+ name + "------"+score);
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }
    //执行完释放资源
    JdbcUtil.close(conn,st,null);
}

防SQL注入改进

使用PrepareStatement 可以防止SQL注入，效率更高(预编译)

package com.ch.jdbc;

import com.ch.util.JdbcUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * 防SQL注入问题的一种改进
 */
public class PreJdbcTest {

    public static void main(String[] args) {
        Connection conn = JdbcUtil.getConnection();
        String sql = "select * from student where `name`= ?";
        PreparedStatement pst = null;
        ResultSet rs = null;
        try {
            pst = conn.prepareStatement(sql);
            pst.setString(1,"张三");

            rs = pst.executeQuery();

            while (rs.next()){
                int id = rs.getInt("id");
                int score = rs.getInt("score");
                String name = rs.getString("name");
                System.out.println(id + "------"+ name + "------"+score);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

}