美文网首页
Java JDBC编程

Java JDBC编程

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

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

    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

    相关文章

      网友评论

          本文标题：Java JDBC编程

          本文链接：https://www.haomeiwen.com/subject/mxukmctx.html

          延伸阅读

          深度阅读

          • 您也可以注册成为美文阅读网的作者，发表您的原创作品、分享您的心情！

          栏目导航

          热点阅读

          关于我们|服务条款|联系我们|Java JDBC编程|投稿指南|网站地图|RSS订阅|排版工具|手机版

          提供经典美文摘抄,优美散文欣赏,现代诗歌精选,短篇小说,心情随笔,表白情书范文,故事会在线阅读欣赏

          Copyright © 2014-2023 Haomeiwen.com All Rights Reserved. 好美文阅读网 版权所有

          备案信息：桂公网安备 45052102000051号 · 桂ICP备13007215号-3

          本站所收录作品、热点评论等信息部分来源互联网，目的只是为了系统归纳学习和传递资讯

          所有作品版权归原创作者所有，与本站立场无关，如不慎侵犯了你的权益，请联系我们告知，我们将做删除处理！