JDBC基础

基本步骤：

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

/*
* JDBC：为sun公司提供的一个访问数据库的包
* */
public class MainTest {
    public static void main(String[] args) throws SQLException, ClassNotFoundException {
        /*
        * 1.注册驱动
        * 在Driver类今天代码块中，new Driver()
        *
        * 在JDK5后 META-INF/services，文件夹中自动注册，可以省略
        * */
        Class<?> aClass = Class.forName("com.mysql.jdbc.Driver");

        //2.获取连接：如果是本机且端口为默认，可以简写jdbc:mysql:///db1
//        Connection conn= DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/db1","root","root");
        Connection conn= DriverManager.getConnection("jdbc:mysql:///db1","root","root");
        String sql="INSERT student (id,NAME,age,score) VALUES(3,\"b\",19,67)";
        //3.获取用于执行静态SQL语句并返回其生成的结果的对象
        Statement statement = conn.createStatement();
        //4.执行aql语句
        int cout = statement.executeUpdate(sql);
        System.out.println(cout);
        //5.释放资源
        statement.close();
        conn.close();
    }
}

增删改：

/*
* 增删改：调用方法statement.executeUpdate(sql)，返回受到影响的行数
* */
public class UpdataTest {
    public static void main(String[] args) {
        Connection conn=null;
        Statement statement=null;
        try {
            Class.forName("com.mysql.jdbc.Driver");

            conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/db1", "root", "root");

            String sql="INSERT student (id,NAME,age,score) VALUES(3,\"b\",19,67);";

            statement = conn.createStatement();
            int res = statement.executeUpdate(sql);
            System.out.println(res);

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

查询；

    /*
    * 查询：调用stat.executeQuery(sql)，返回ResultSet，ResultSet为一个结果集。
    * */
    private static void test1() {
        Connection conn=null;
        Statement stat=null;
        ResultSet rs=null;
        try {
            Class.forName("com.mysql.jdbc.Driver");

            conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/db1", "root", "root");
            String sql="SELECT * FROM student";
            stat = conn.createStatement();
            rs = stat.executeQuery(sql);
            List<Student> list=new ArrayList<>();
            Student stu=null;
            while (rs.next()){
                //可以通过列下标来获取也可以通过属性名称
                //int id=rs.getInt(1);

                stu=new Student();
                stu.setId(rs.getInt("id"));
                stu.setAge(rs.getInt("age"));
                stu.setName(rs.getString("name"));
                stu.setBirthday(rs.getDate("birthday"));
                stu.setInsert_time(rs.getDate("insert_time"));
                stu.setScore(rs.getDouble("score"));
                list.add(stu);
            }

            System.out.println(list.size());

            System.out.println(list);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            if (rs!=null){
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (stat!=null){
                try {
                    stat.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (conn!=null){
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

带参数查询：

    /*
    * 带参数化查询：使用的是conn.prepareStatement(sql)获取的PreparedStatement对象。
    * 与Statement对比：
    * 1.Statement:是执行静态的sql语句，及不带参数的sql语句，可以用字符串拼接完成带参数，缺点：sql注入
    * 2.PreparedStatement:预加载sql，参数用？，表示。用pstmt.setString(1,"0")来加入参数。
    * */
    private static void test2() {
        Connection conn=null;
        PreparedStatement pstmt = null;
        ResultSet rs=null;
        try {
            Class.forName("com.mysql.jdbc.Driver");

            conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/db1", "root", "root");
            //sql语句通过？代表参数
            String sql="SELECT * FROM student WHERE score>?";

            //预加载sql语句
            pstmt = conn.prepareStatement(sql);
            //根据❓下标，将参数设置
            pstmt.setString(1,"0");
            //执行带参数sql语句
            rs = pstmt.executeQuery();
            List<Student> list=new ArrayList<>();
            Student stu=null;
            while (rs.next()){
                stu=new Student();
                stu.setId(rs.getInt("id"));
                stu.setAge(rs.getInt("age"));
                stu.setName(rs.getString("name"));
                stu.setBirthday(rs.getDate("birthday"));
                stu.setInsert_time(rs.getDate("insert_time"));
                stu.setScore(rs.getDouble("score"));
                list.add(stu);
            }

            System.out.println(list.size());

            System.out.println(list);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            if (rs!=null){
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (pstmt!=null){
                try {
                    pstmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (conn!=null){
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

JDBC中connect和close封装：

封装类：

import java.io.FileReader;
import java.io.IOException;
import java.net.URL;
import java.sql.*;
import java.util.Properties;


public class JDBCUitls {
    private static String user;
    private static String password;
    private static String url;
    private static String driver;

    /*
    * 静态代码块
    * 内加载的时候执行，并且只执行一次。可实现单例模式
    * */
    static {

        try {
            //获取类加载器
            ClassLoader clsLoader = JDBCUitls.class.getClassLoader();
            //在JDBCUitls所在的src文件夹，第一级目录下查找jdbc.properties文件
            URL resource = clsLoader.getResource("jdbc.properties");
            //返回文件绝对路径
            String path = resource.getPath();
            //Properties加载文件
            Properties prop=new Properties();
            prop.load(new FileReader(path));

            user = prop.getProperty("user");
            password=prop.getProperty("password");
            url=prop.getProperty("url");
            driver=prop.getProperty("driver");

            Class.forName(driver);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    /*
    * 连接方法
    * */
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url, user, password);
    }

    /*
    * 释放链接
    * */
    public static void close(Connection conn, PreparedStatement pstmt, ResultSet rs){
        if (rs!=null){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (pstmt!=null){
            try {
                pstmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (conn!=null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /*
     * 释放链接
     * */
    public static void close(Connection conn, Statement stmt){

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

调用方法：

    /*
    * 封装Connect和close
    * */
    private static void test3() {
        Connection conn=null;
        PreparedStatement pstmt = null;
        ResultSet rs=null;
        try {
            conn = JDBCUitls.getConnection();
            //sql语句通过？代表参数
            String sql="SELECT * FROM student WHERE score>?";

            //预加载sql语句
            pstmt = conn.prepareStatement(sql);
            //根据❓下标，将参数设置
            pstmt.setString(1,"0");
            //执行带参数sql语句
            rs = pstmt.executeQuery();
            List<Student> list=new ArrayList<>();
            Student stu=null;
            while (rs.next()){
                stu=new Student();
                stu.setId(rs.getInt("id"));
                stu.setAge(rs.getInt("age"));
                stu.setName(rs.getString("name"));
                stu.setBirthday(rs.getDate("birthday"));
                stu.setInsert_time(rs.getDate("insert_time"));
                stu.setScore(rs.getDouble("score"));
                list.add(stu);
            }

            System.out.println(list.size());

            System.out.println(list);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUitls.close(conn,pstmt,rs);
        }
    }

 

JDBC事物：

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


/*
* MySql中事物默认是自动提交的:每条sql语句执行完成后自动提交
* 修改事务的默认提交方式：
 * 查看事务的默认提交方式：SELECT @@autocommit; -- 1 代表自动提交  0 代表手动提交
 * 修改默认提交方式： set @@autocommit = 0;
 *
 * JDBC中用：
 * conn.setAutoCommit(false);---设置事物为手动提交
 * conn.commit();---手动提交事物
 * conn.rollback();--回滚事物
* */
public class TransTest {
    public static void main(String[] args) {
        String sql1="UPDATE student SET age=age+5 WHERE NAME='a'";
        String sql2="UPDATE student SET age=age-5 WHERE NAME='c'";

        Connection conn=null;
        Statement stmt = null;
        try {
            conn = JDBCUitls.getConnection();
            conn.setAutoCommit(false);
            stmt = conn.createStatement();
            int res1 = stmt.executeUpdate(sql1);
//            int i=3/0;
            int res2 = stmt.executeUpdate(sql2);
            conn.commit();
        } catch (SQLException e) {
            try {
                conn.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
            e.printStackTrace();
        }finally {
            JDBCUitls.close(conn,stmt);
        }
    }
}

 

Druid连接池应用：

import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Properties;

/*
* Druid包为阿里高性能连接池包
* 使用步骤：
* 1.导入包：druid-1.0.9.jar。
* 2.添加配置文件：druid.properties
* 3.获取连接
* */
public class DruidTest {
    public static void main(String[] args) throws Exception {
        long start = System.currentTimeMillis();
        for (int i=0;i<100;i++){
            test1();
        }
        long end = System.currentTimeMillis();
        System.out.println(end-start);

    }

    private static void test1(){
        String sql="INSERT student (id,NAME,age,score) VALUES(?,?,?,?)";
        Connection conn=null;
        PreparedStatement pstmt=null;
        try {
            conn = JDBCUtils.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1,"4");
            pstmt.setString(2,"abc");
            pstmt.setString(3,String.valueOf(20));
            pstmt.setString(4,String.valueOf(99.5));
            int res = pstmt.executeUpdate();
            System.out.println(res);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.close(conn,pstmt);
        }
    }

    private static void test() throws Exception {
        //1.加载类加载器
        ClassLoader clsLoader = DruidTest.class.getClassLoader();
        //2.加载配置文件：配置文件的名字可以修改
        InputStream resourceAsStream = clsLoader.getResourceAsStream("druid.properties");
        //3.Properties加载配置文件流
        Properties prop=new Properties();
        prop.load(resourceAsStream);

        //4.获取连接池
        DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
        //5.获取连接
        Connection connection = dataSource.getConnection();
        System.out.println(connection);
        //6.释放连接
        connection.close();
    }
}

封装工具类：

import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class JDBCUtils {
    private static DataSource ds;
    static {

        try {
            Properties pro=new Properties();
            pro.load(JDBCUtils.class.getClassLoader().getResourceAsStream("druid.properties"));

            ds = DruidDataSourceFactory.createDataSource(pro);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnection() throws SQLException {
        return ds.getConnection();
    }

    public static void close(Connection conn, Statement stmt, ResultSet rs){
        if (rs!=null){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (stmt!=null){
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (conn!=null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public static void close(Connection conn, Statement stmt){
        close(conn,stmt,null);
    }
    public static DataSource getDataSource(){
        return ds;
    }
}

 

JdbcTemplate操作：

import org.junit.Test;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;


/*
* 步骤：
* 1.引入5个包：commons-logging-1.2.jar、spring-beans-5.0.0.RELEASE.jar
* spring-core-5.0.0.RELEASE.jar、spring-jdbc-5.0.0.RELEASE.jar、spring-tx-5.0.0.RELEASE.jar
* 2.获取JdbcTemplate：new JdbcTemplate(JDBCUtils.getDataSource())
* 3.操作
* */
public class JDBDSpring {

    private JdbcTemplate template=new JdbcTemplate(JDBCUtils.getDataSource());

    /*
    * 增删改的操作
    * */
    @Test
    public void testUptate(){

        String sql="INSERT student (id,NAME,age,score,birthday) VALUES(NULL,?,?,?,?)";
        SimpleDateFormat format=new SimpleDateFormat("yyyy-MM-dd");

        int res = template.update(sql, "阿斯蒂芬", 20, 99, format.format( new Date().getTime()));
        System.out.println(res);
    }

    /*
    * 手动处理获取集合
    * */
    @Test
    public void testQuery(){
        String sql="SELECT * FROM student WHERE score>?";
        List<Student> list = template.query(sql, new RowMapper<Student>() {
            @Override
            public Student mapRow(ResultSet resultSet, int i) throws SQLException {
                Student stu=new Student();
                stu.setScore(resultSet.getDouble("score"));
                stu.setInsert_time(resultSet.getDate("insert_time"));
                stu.setBirthday(resultSet.getDate("birthday"));
                stu.setName(resultSet.getString("name"));
                stu.setAge(resultSet.getInt("age"));
                stu.setId(resultSet.getInt("id"));
                return stu;
            }
        },60);

        for (Student stu:list){
            System.out.println(stu.toString());
        }

    }

    /*
    * 自动转换为对象
    * 注意对空的处理，属性必须也能接收null类型
    * */
    @Test
    public void testQuery1(){
        String sql="SELECT * FROM student WHERE score>?";
        List<Student> list = template.query(sql, new BeanPropertyRowMapper<Student>(Student.class),60);
        for (Student stu:list){
            System.out.println(stu.toString());
        }
    }

    /*
    * 返回单行单列数据
    * */
    @Test
    public void testCount(){
        String sql="SELECT COUNT(id) FROM student";
        Long o = template.queryForObject(sql,Long.class);
        System.out.println(o);
    }

    /*
    * 返回列表：
    * 将没列的属性和值对应放入Map集合
    * */
    @Test
    public void testQueryForList(){
        String sql="SELECT * FROM student WHERE score>?";
        List<Map<String, Object>> maps = template.queryForList(sql, 60);
        for (Map<String,Object> map:maps
             ) {
            System.out.println(map);
        }
    }

    /*
    * 查询返回一条数据：如果不是一条数据会报错
    * 返回值：以键值对保存在Map中，键属性名，值属性值
    * */
    @Test
    public void testQueryForMap(){
        String sql="SELECT * FROM student WHERE score>? limit 0,1";
        Map<String, Object> map = template.queryForMap(sql, 60);
        for (Object o:map.values()){
            System.out.println(o);
        }
    }
}