JDBC入门笔记

来源：互联网发布：linux重启网卡编辑：程序博客网时间：2024/05/29 23:25

以下为JDBC入门笔记：

主要的测试代码

import java.sql.*;public class Main {    public static void main(String[] args) {        insert();        PrepareInsert(0,"lewis");        query();        ProcedureTest();        ProcedureTest(0,"000000000");        query();    }    public static void print(String str){        System.out.println(str);    }    public static void insert(){                //插入        Connection conn = null;        Statement stmt=null;        String sql="insert into view(View,url_Link) values(122,'hahhahha');";        try {            conn = DBUtil.open();            stmt=conn.createStatement();            stmt.execute(sql);                      //返回bool类型        } catch (SQLException e) {            e.printStackTrace();        } finally{            try {                if(stmt!=null) {                    stmt.close();                }                if(conn!=null){                    conn.close();                }            } catch (SQLException e) {                e.printStackTrace();            }        }    }    public static void query(){                         //查询        Connection conn = null;        Statement stmt = null;        ResultSet rs = null;        String sql = "select * from view";        try {            conn=DBUtil.open();            stmt = conn.createStatement();            rs = stmt.executeQuery(sql);          //返回ResultSet            while(rs.next()){                int View = rs.getInt(1);                String url_Link = rs.getString(2);                print("View"+View+":"+"url_Link"+url_Link);            }        } catch (SQLException e) {            e.printStackTrace();        } finally {           try {               if(conn!=null)                   conn.close();               if(stmt!=null)                   stmt.close();               if(rs!=null)                   rs.close();           } catch (SQLException e) {               e.printStackTrace();           }        }    }    public static void PrepareInsert(int view,String link){        String sql="insert into view(View,url_Link) values(?,?);";//问号作为占位符        Connection conn = null;        PreparedStatement pstmt = null;        try {            conn = DBUtil.open();            pstmt = conn.prepareStatement(sql);            pstmt.setInt(1,view);            pstmt.setString(2,link);                    //设置占位符的变量            pstmt.executeUpdate();        } catch (SQLException e) {            e.printStackTrace();        } finally{            try {                if(conn!=null)                    conn.close();                if(pstmt!=null)                    pstmt.close();            } catch (SQLException e) {                e.printStackTrace();            }        }    }    public static void ProcedureTest(){             //存储过程测试        Connection conn = null;        CallableStatement cstmt =null;        ResultSet rs = null;        try {            conn = DBUtil.open();            cstmt = conn.prepareCall("{call find_all()}");//调用存储过程            rs = cstmt.executeQuery();            while(rs.next()){                int View = rs.getInt(1);                String url_Link = rs.getString(2);                print("View"+View+":"+"url_Link"+url_Link);            }        } catch (SQLException e) {            e.printStackTrace();        } finally{            try {                if(conn!=null){                    conn.close();                }                if(cstmt!=null){                    cstmt.close();                }                if(rs!=null){                    rs.close();                }            } catch (SQLException e) {                e.printStackTrace();            }        }    }    public static void ProcedureTest(int view,String link_url){             //存储过程测试        Connection conn = null;        CallableStatement cstmt=null;        try {            conn = DBUtil.open();            cstmt = conn.prepareCall("{call insert_bili(?,?)}");//调用存储过程            cstmt.setInt(1,view);            cstmt.setString(2,link_url);            cstmt.executeUpdate();        } catch (SQLException e) {            e.printStackTrace();        } finally {            try {                if (conn!=null)                    conn.close();                if (cstmt!=null)                    cstmt.close();            } catch (SQLException e) {                e.printStackTrace();            }        }    }}

链接数据库类

import java.io.FileNotFoundException;import java.io.FileReader;import java.io.IOException;import java.io.Reader;import java.sql.Connection;import java.sql.DriverManager;import java.sql.SQLException;import java.util.Properties;/** * Created by lewis on 2016/10/7. */public class DBUtil {    private static String driver;    private static String url;    private static String username;    private static String passwd;    static{                                                                                //静态代码块，用于数据的初始化        Properties prop = new Properties();        try {            Reader in = new FileReader("src//config.properties");                       //使用配置文件初始化数据库的链接            prop.load(in);        } catch (FileNotFoundException e) {            e.printStackTrace();        } catch (IOException e) {            e.printStackTrace();        }        driver=prop.getProperty("driver");        url=prop.getProperty("url");        username=prop.getProperty("username");        passwd=prop.getProperty("passwd");    }    public static Connection open(){        try {            Class.forName(driver);            return DriverManager.getConnection(url,username,passwd);        } catch (ClassNotFoundException e) {            e.printStackTrace();        } catch (SQLException e) {            e.printStackTrace();        }        return null;    }}

SQL脚本文件

create database bilibili_db;                                --创建数据库use bilibili_db;CREATE TABLE view                                           --建表(  View int,  url_Link varchar(100))ENGINE=MyISAM DEFAULT CHARSET=utf8;CREATE PROCEDURE find_all() SELECT * FROM view;           --创建存储过程call find_all()                                              --MySQL中调用存储过程，（测试用）CREATE PROCEDUREinsert_bili(IN viewr INT ,IN url VARCHAR (100))INSERT INTO view(View,url_Link) VALUES (viewr,url);

SQL配置文件，通过读取配置文件来链接数据库，方便修改链接数据库的配置

driver=com.mysql.jdbc.Driverurl=jdbc:mysql://localhost:3306/bilibili_dbusername=rootpasswd=

0 0