Java JDBC编程

作者: 一亩三分甜 | 来源:发表于2019-10-13 20:45 被阅读0次

1-JDBC是什么
Mybatis从入门到精通①——从JDBC编程开始
MyBatis详解1.概述
Mybatis从入门到精通①——从JDBC编程开始
JDBC与MyBatis
理解JDBC——Java Database Connectivi
JDBC实用知识--1
JAVA技术知识体系
2019-04-29 Java_JDBC_0
Spring+SpringMVC+MyBatis+easyUI整

每天一点点，感受自己存在的意义。

JDBC编程步骤

1.Load the Driver

1.Class.forName()|Class.forName().newInstance()|new DriverName()

2.实例化时自动向DriverManager注册，不需显式调用DriverManager.registerDriver方法。

2.Connect to the DataBase

1.DriverManager.getConnection();

3.Execute the SQL

1.Connection.CreateStatement();

2.Statement.executeQuery();

3.Statement.executeUpdate();

4.Retrieve the result data

1.循环取得结果while(rs.next())

5.Show the result data

1.将数据库中的各种类型转换为Java中的类型(getXXX)方法。

6.Close

1.close the resultset & close the statement & close the connection

import java.sql.*;
public class JDBCStart {
    //JDBC driver name and database URL
    static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
    static final String DB_URL = "jdbc:mysql://localhost:3306/firstDB";
    static final String USER = "root";
    static final String PASS = "1234567890";

    public static void main(String[] args)
    {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try{
            Class.forName("com.mysql.jdbc.Driver");
            System.out.println("Connecting to database...");
            conn = DriverManager.getConnection(DB_URL,USER,PASS);
            System.out.println("Creating statement...");
            stmt = conn.createStatement();
            String sql;
            sql = "select ID,name,age,BIRTHDAY from person";
            rs = stmt.executeQuery(sql);
            while (rs.next())
            {
               int id = rs.getInt("ID");
               String name = rs.getString("name");
               int age = rs.getInt("age");
               String birthday = rs.getString("BIRTHDAY");
               System.out.println("ID:"+id);
               System.out.println("name:"+name);
               System.out.println("age:"+age);
               System.out.println("BIRTHDAY:"+birthday);
            }
            rs.close();
            stmt.close();
            conn.close();
        }catch (SQLException se)
        {
            se.printStackTrace();
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }finally {
            try {
                if (stmt!=null)
                {
                    stmt.close();
                    stmt = null;  
                }
            }catch (SQLException se2)
            {
                se2.printStackTrace();
            }
            try
            {
                if (conn!=null)
                {
                    conn.close();
                    conn = null;
                }
            }
            catch (SQLException se)
            {
               se.printStackTrace();
            }
            try{
                if (rs!=null)
                {
                    rs.close();
                    rs = null;
                }
            }catch (SQLException se1)
            {
                se1.printStackTrace();
            }
        }
        System.out.println("try finish");
    }
}
//输出
Connecting to database...
Creating statement...
ID:4
name:Nick
age:24
BIRTHDAY:1990-05-22 00:00:00.0
ID:5
name:Rick
age:24
BIRTHDAY:1991-05-22 00:00:00.0
ID:6
name:Anny
age:22
BIRTHDAY:1992-05-22 00:00:00.0
ID:7
name:Calvin
age:23
BIRTHDAY:1992-05-22 00:00:00.0
ID:8
name:Lisa
age:23
BIRTHDAY:1992-05-22 00:00:00.0
ID:9
name:Kerry
age:33
BIRTHDAY:null
try finish

将参数添加到数据库语句中

import java.sql.*;

public class JDBCTest {
    //JDBC driver name and database URL
    static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
    static final String DB_URL = "jdbc:mysql://localhost:3306/firstDB";
    static final String USER = "root";
    static final String PASS = "1234567890";
    public static void main(String[] args)
    {
        if (args.length != 4)
        {
            System.out.println("Parameter Error! Please Input Again!");
            System.exit(-1);
        }
        int id = 0;
        try {
            id = Integer.parseInt(args[0]);
        }catch (NumberFormatException e)
        {
            System.out.println("Parameter Error! Deptno should be Number Format!");
            System.exit(-1);
        }
        String name = args[1];
        int age = 0;
        try{
           age = Integer.parseInt(args[2]);
        }catch (NumberFormatException e)
        {
            System.out.println("Parameter Error! Deptno should be Number Format!");
            System.exit(-1);
        }
        String date = args[3];
        Connection conn = null;
        Statement stmt = null;
        try{
        Class.forName("com.mysql.jdbc.Driver");
        System.out.println("Connecting to database...");
        conn = DriverManager.getConnection(DB_URL,USER,PASS);
        System.out.println("Creating statement...");
        stmt = conn.createStatement();
        String sql;
        sql = "insert into person VALUES (" + id +",'"+ name + "',"+age+",'"+date+"')";
        stmt.executeUpdate(sql);
        stmt.close();
        conn.close();
        }catch (SQLException se0)
        {
            se0.printStackTrace();
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }finally {
            try {
                if (stmt!=null)
                {
                    stmt.close();
                    stmt = null;
                }
            }catch (SQLException se2)
            {
                se2.printStackTrace();
            }
            try
            {
                if (conn!=null)
                {
                    conn.close();
                    conn = null;
                }
            }
            catch (SQLException se)
            {
                se.printStackTrace();
            }
        }
        System.out.println("try finish");
    }
}
//输出
Connecting to database...
Creating statement...
try finish

Snip20191013_4.png

灵活指定SQL语句中的变量PreparedStatement

import java.sql.*;

public class JDBCPreparedStatement {
    //JDBC driver name and database URL
    static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
    static final String DB_URL = "jdbc:mysql://localhost:3306/firstDB";
    static final String USER = "root";
    static final String PASS = "1234567890";
    public static void main(String[] args)
    {
        if (args.length != 4)
        {
            System.out.println("Parameter Error! Please Input Again!");
            System.exit(-1);
        }
        int id = 0;
        try {
            id = Integer.parseInt(args[0]);
        }catch (NumberFormatException e)
        {
            System.out.println("Parameter Error! Deptno should be Number Format!");
            System.exit(-1);
        }
        String name = args[1];
        int age = 0;
        try{
            age = Integer.parseInt(args[2]);
        }catch (NumberFormatException e)
        {
            System.out.println("Parameter Error! Deptno should be Number Format!");
            System.exit(-1);
        }
        String date = args[3];
        Connection conn = null;
        PreparedStatement pstmt = null;
        try{
            Class.forName("com.mysql.jdbc.Driver");
            System.out.println("Connecting to database...");
            conn = DriverManager.getConnection(DB_URL,USER,PASS);
            System.out.println("Creating statement...");
            String sql;
            sql = "insert into person VALUES (?,?,?,?)";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1,id);
            pstmt.setString(2,name);
            pstmt.setInt(3,22);
            pstmt.setString(4,"1990-08-10");
            pstmt.executeUpdate();
        }catch (SQLException se0)
        {
            se0.printStackTrace();
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }finally {
            try {
                if (pstmt!=null)
                {
                    pstmt.close();
                    pstmt = null;
                }
            }catch (SQLException se2)
            {
                se2.printStackTrace();
            }
            try
            {
                if (conn!=null)
                {
                    conn.close();
                    conn = null;
                }
            }
            catch (SQLException se)
            {
                se.printStackTrace();
            }
        }
        System.out.println("try finish");
    }
}
//输出
Connecting to database...
Creating statement...
try finish

Snip20191013_5.png

JDBC处理存储过程

在MYSQL WorkBench创建存储过程。

0.gif

CREATE PROCEDURE `pro_findById`(sid int)
BEGIN
select * from PERSON where (id = sid);
END

1.调用带有输入参数的存储过程

import java.sql.*;

public class JDBCCallableStatement {
    //JDBC driver name and database URL
    static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
    static final String DB_URL = "jdbc:mysql://localhost:3306/firstDB";
    static final String USER = "root";
    static final String PASS = "1234567890";
    public static void main(String[] args)
    {
        Connection conn = null;
        CallableStatement cstmt = null;
        ResultSet rs = null;
        try{
            Class.forName("com.mysql.jdbc.Driver");
            System.out.println("Connecting to database...");
            conn = DriverManager.getConnection(DB_URL,USER,PASS);
            System.out.println("Creating statement...");
            String sql;
            sql = "call pro_findById(?)";
            cstmt = conn.prepareCall(sql);
            cstmt.setInt(1,4);
            rs = cstmt.executeQuery();
            while (rs.next())
            {
                int id = rs.getInt("ID");
                String name = rs.getString("name");
                int age = rs.getInt("age");
                String birthday = rs.getString("BIRTHDAY");
                System.out.println("-id-:"+id+"-name-"+name+"-age-:"+age+"-birthday-"+birthday);
            }
        }catch (SQLException se0)
        {
            se0.printStackTrace();
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }finally {
            try {
                if (cstmt!=null)
                {
                    cstmt.close();
                    cstmt = null;
                }
            }catch (SQLException se2)
            {
                se2.printStackTrace();
            }
            try
            {
                if (conn!=null)
                {
                    conn.close();
                    conn = null;
                }
            }
            catch (SQLException se)
            {
                se.printStackTrace();
            }
        }
        System.out.println("try finish");
    }
}
//输出
Connecting to database...
Creating JDBCCallableStatement...
-id-:4-name-Nick-age-:25-birthday-1992-05-22 00:00:00.0
try finish

2 带有两个输入参数的存储过程

import java.sql.*;

public class JDBCCallableStatement0 {
    //JDBC driver name and database URL
    static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
    static final String DB_URL = "jdbc:mysql://localhost:3306/firstDB";
    static final String USER = "root";
    static final String PASS = "1234567890";
    public static void main(String[] args)
    {
        Connection conn = null;
        CallableStatement cstmt = null;
        ResultSet rs = null;
        try{
            Class.forName("com.mysql.jdbc.Driver");
            System.out.println("Connecting to database...");
            conn = DriverManager.getConnection(DB_URL,USER,PASS);
            System.out.println("Creating statement...");
            String sql;
            sql = "call pro_findByIdAndAge(?,?)";
            cstmt = conn.prepareCall(sql);
            cstmt.setInt(1,4);
            cstmt.setInt(2,26);
            rs = cstmt.executeQuery();
            while (rs.next())
            {
                int id = rs.getInt("ID");
                String name = rs.getString("name");
                int age = rs.getInt("age");
                String birthday = rs.getString("BIRTHDAY");
                System.out.println("-id-:"+id+"-name-"+name+"-age-:"+age+"-birthday-"+birthday);
            }
        }catch (SQLException se0)
        {
            se0.printStackTrace();
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }finally {
            try {
                if (cstmt!=null)
                {
                    cstmt.close();
                    cstmt = null;
                }
            }catch (SQLException se2)
            {
                se2.printStackTrace();
            }
            try
            {
                if (conn!=null)
                {
                    conn.close();
                    conn = null;
                }
            }
            catch (SQLException se)
            {
                se.printStackTrace();
            }
        }
        System.out.println("try finish");
    }
}
输出
Connecting to database...
Creating JDBCCallableStatement...
-id-:4-name-Nick-age-:25-birthday-1992-05-22 00:00:00.0
-id-:5-name-Rick-age-:26-birthday-1992-05-22 00:00:00.0
-id-:6-name-Jim-age-:26-birthday-1992-05-20 00:00:00.0
try finish

3 带有输出参数的存储过程
创建存储过程

delimiter $
CREATE PROCEDURE `pro_findById0` (IN sid int,OUT sname varchar(20))
BEGIN
select name into sname from PERSON where id = sid;
END
$

正常查询输出id = 4的name为Nick。

import java.sql.*;

public class JDBCCallableStatement1 {
    //JDBC driver name and database URL
    static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
    static final String DB_URL = "jdbc:mysql://localhost:3306/firstDB?noAccessToProcedureBodies=true";
    static final String USER = "root";
    static final String PASS = "123";
    public static void main(String[] args)
    {
        Connection conn = null;
        CallableStatement cstmt = null;
        ResultSet rs = null;
        try{
            Class.forName("com.mysql.jdbc.Driver");
            System.out.println("Connecting to database...");
            conn = DriverManager.getConnection(DB_URL,USER,PASS);
            System.out.println("Creating JDBCCallableStatement1...");
            String sql;
            sql = "call pro_findById0(?,?)";
            cstmt = conn.prepareCall(sql);
            cstmt.setInt(1,4);
            cstmt.registerOutParameter(2, java.sql.Types.VARCHAR);
            cstmt.executeQuery();
            String result = cstmt.getString(2);
            System.out.println(result);
        }catch (SQLException se0)
        {
            se0.printStackTrace();
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }finally {
            try {
                if (cstmt!=null)
                {
                    cstmt.close();
                    cstmt = null;
                }
            }catch (SQLException se2)
            {
                se2.printStackTrace();
            }
            try
            {
                if (conn!=null)
                {
                    conn.close();
                    conn = null;
                }
            }
            catch (SQLException se)
            {
                se.printStackTrace();
            }
        }
        System.out.println("try finish");
    }
}

输出
Connecting to database...
Creating JDBCCallableStatement1...
Nick
try finish

批处理

import java.sql.*;
public class JDBCBatch {
    //JDBC driver name and database URL
    static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
    static final String DB_URL = "jdbc:mysql://127.0.0.1:3306/firstDB?useSSL=false&useUnicode=true&characterEncoding=UTF-8";
    static final String USER = "root";
    static final String PASS = "123";

    public static void main(String[] args)
    {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try{
            Class.forName("com.mysql.jdbc.Driver");
            System.out.println("Connecting to database...");
            conn = DriverManager.getConnection(DB_URL,USER,PASS);
            System.out.println("Creating statement...");
            stmt = conn.createStatement();
            stmt.addBatch("insert into book values(5,'百年孤独',51,600)");
            stmt.addBatch("insert into book values(6,'荒野猎人',52,550)");
            stmt.addBatch("insert into book values(7,'从你的全世界路过',53,580)");
            stmt.executeBatch();
            stmt.close();
            conn.close();
        }catch (SQLException se)
        {
            se.printStackTrace();
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }finally {
            try {
                if (stmt!=null)
                {
                    stmt.close();
                    stmt = null;
                }
            }catch (SQLException se2)
            {
                se2.printStackTrace();
            }
            try
            {
                if (conn!=null)
                {
                    conn.close();
                    conn = null;
                }
            }
            catch (SQLException se)
            {
                se.printStackTrace();
            }
            try{
                if (rs!=null)
                {
                    rs.close();
                    rs = null;
                }
            }catch (SQLException se1)
            {
                se1.printStackTrace();
            }
        }
        System.out.println("try finish");
    }
}
//输出
Connecting to database...
Creating statement...
try finish

import java.sql.*;

public class JDBCBatch0 {
    //JDBC driver name and database URL
    static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
    static final String DB_URL = "jdbc:mysql://127.0.0.1:3306/firstDB?useSSL=false&useUnicode=true&characterEncoding=UTF-8";
    static final String USER = "root";
    static final String PASS = "123";

    public static void main(String[] args)
    {
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try{
            Class.forName("com.mysql.jdbc.Driver");
            System.out.println("Connecting to database...");
            conn = DriverManager.getConnection(DB_URL,USER,PASS);
            System.out.println("Creating statement...");
            stmt = conn.prepareStatement("insert into book values(?,?,?,?);");
            stmt.setInt(1,8);
            stmt.setString(2,"平凡的世界");
            stmt.setFloat(3,59);
            stmt.setInt(4,1370);
            stmt.addBatch();

            stmt.setInt(1,9);
            stmt.setString(2,"人生");
            stmt.setFloat(3,62);
            stmt.setInt(4,240);
            stmt.addBatch();

            stmt.setInt(1,10);
            stmt.setString(2,"悲惨世界");
            stmt.setFloat(3,45);
            stmt.setInt(4,370);
            stmt.addBatch();

            stmt.executeBatch();
            stmt.close();
            conn.close();
        }catch (SQLException se)
        {
            se.printStackTrace();
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }finally {
            try {
                if (stmt!=null)
                {
                    stmt.close();
                    stmt = null;
                }
            }catch (SQLException se2)
            {
                se2.printStackTrace();
            }
            try
            {
                if (conn!=null)
                {
                    conn.close();
                    conn = null;
                }
            }
            catch (SQLException se)
            {
                se.printStackTrace();
            }
            try{
                if (rs!=null)
                {
                    rs.close();
                    rs = null;
                }
            }catch (SQLException se1)
            {
                se1.printStackTrace();
            }
        }
        System.out.println("try finish");
    }
}
//输出
Connecting to database...
Creating statement...
try finish

Snip20191109_3.png

1-JDBC是什么
JDBC概念 JDBC (Java Database Connectivity) API，即Java数据库编程接口...
Mybatis从入门到精通①——从JDBC编程开始
[toc] JDBC是什么 (Java DataBase Connectivity) jdbc是一种Java编程语...
MyBatis详解1.概述
点击进入我的博客 1 JDBC编程什么是JDBC Java程序都是通过JDBC(Java Data Base C...
Mybatis从入门到精通①——从JDBC编程开始
JDBC是什么 (Java DataBase Connectivity) jdbc是一种Java编程语言和各种数据...
JDBC与MyBatis
传统的JDBC编程 JAVA程序通过JDBC链接数据库，这样我们就可以通过SQL对数据库进行编程。 JAVA链接数...
理解JDBC——Java Database Connectivi
JDBC是什么？ JDBC，是一种用于Java编程语言和多种数据库连接的标准Java API。JDBC库提供的多种...
JDBC实用知识--1
JDBC Java Database Connectivity (JDBC))是一个应用程序编程接口(API)的J...
JAVA技术知识体系
java基础 java虚拟机集合框架网络编程-Socket 并发编程-Thread io 数据库操作-JDBC...
2019-04-29 Java_JDBC_0
Java程序访问数据库：①：import java.sql,*; JDBC链接数据库涉及到一个JDBC连接的编程...
Spring+SpringMVC+MyBatis+easyUI整
在java程序与数据库连接的编程中，mysql jdbc url格式如下：如jdbc:mysql://local...