Extracts JDBC tool classes, adds, deletes, modifies, and queries, and extracts jdbc tool classes.

Extracts JDBC tool classes, adds, deletes, modifies, and queries, and extracts jdbc tool classes.

Extraction Tool:

Package demo;/** tool class */import java. SQL. connection; import java. SQL. driverManager; import java. SQL. preparedStatement; import java. SQL. resultSet; public class JDBCUtils1 {public static Connection getConnection () {Connection conn = null; try {Class. forName ("com. mysql. jdbc. driver "); String url =" jdbc: mysql: // localhost: 3306/mybase "; String username =" root "; String password =" xuyiqing "; conn = DriverM Anager. getConnection (url, username, password);} catch (Exception ex) {ex. printStackTrace () ;}return conn;} public static void release (Connection conn, PreparedStatement pstmt, ResultSet rs) {if (rs! = Null) {try {rs. close () ;}catch (Exception e) {e. printStackTrace () ;}} if (pstmt! = Null) {try {pstmt. close () ;}catch (Exception e) {e. printStackTrace () ;}} if (conn! = Null) {try {conn. close ();} catch (Exception e) {e. printStackTrace ();}}}}

View Code

 

Data preparation:

CREATE DATABASE mybase;USE mybase;CREATE TABLE users(uid INT PRIMARY KEY AUTO_INCREMENT,username VARCHAR(64),upassword VARCHAR(64));INSERT INTO users (username,upassword) VALUES("zhangsan","123"),("lisi","456"),("wangwu","789");SELECT * FROM users;

View Code

 

Query by primary key id:

package demo;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import org.junit.Test;public class TestUtils {    @Test    public void testFindUserById() {        Connection conn = null;        PreparedStatement pstmt = null;        ResultSet rs = null;        try {            conn = JDBCUtils1.getConnection();            String sql = "select * from users where uid=?";            pstmt = conn.prepareStatement(sql);            pstmt.setInt(1, 2);            rs = pstmt.executeQuery();            while (rs.next()) {                System.out.println(rs.getString(2) + "----" + rs.getString("upassword"));            }        } catch (Exception ex) {            ex.printStackTrace();        } finally {            JDBCUtils1.release(conn, pstmt, rs);        }    }}

View Code

 

Method 2:

Configuration file (src directory): db. properties

driver=com.mysql.jdbc.Driverurl=jdbc:mysql://localhost:3306/mybaseusername=rootpassword=xuyiqing

View Code

 

Tool class:

package demo;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.util.ResourceBundle;public class JDBCUtils2 {        public static String driver;    public static String url;    public static String username;    public static String password;        static{        ResourceBundle bundle = ResourceBundle.getBundle("db");        driver = bundle.getString("driver");        url = bundle.getString("url");        username = bundle.getString("username");        password = bundle.getString("password");    }        public static Connection getConnection() {        Connection conn = null;        try {            Class.forName(driver);            conn = DriverManager.getConnection(url, username, password);        } catch (Exception ex) {            ex.printStackTrace();        }        return conn;    }    public static void release(Connection conn, PreparedStatement pstmt, ResultSet rs) {        if (rs != null) {            try {                rs.close();            } catch (Exception e) {                e.printStackTrace();            }        }        if (pstmt != null) {            try {                pstmt.close();            } catch (Exception e) {                e.printStackTrace();            }        }        if (conn != null) {            try {                conn.close();            } catch (Exception e) {                e.printStackTrace();            }        }    }}

View Code

 

Insert data:

Package demo; import java. SQL. connection; import java. SQL. preparedStatement; import org. junit. test; public class TestUtils {@ Test public void testAdd () {Connection conn = null; PreparedStatement pstmt = null; try {conn = JDBCUtils2.getConnection (); string SQL = "insert into users values (null ,?,?) "; Pstmt = conn. prepareStatement (SQL); pstmt. setString (1, "xiaoming"); pstmt. setString (2, "666"); int row = pstmt.exe cuteUpdate (); if (row> 0) {System. out. println ("successfully added");} else {System. out. println ("failed to add") ;}} catch (Exception e) {throw new RuntimeException (e) ;}finally {JDBCUtils2.release (conn, pstmt, null );}}}

View Code

 

 

Method 3:

Still use the db. properties configuration file:

package demo;import java.io.InputStream;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.util.Properties;public class JDBCUtils3 {    public static String driver;    public static String url;    public static String username;    public static String password;    static {        try {            ClassLoader classLoader = JDBCUtils3.class.getClassLoader();            InputStream is = classLoader.getResourceAsStream("db.properties");            Properties props = new Properties();            props.load(is);            driver = props.getProperty("driver");            url = props.getProperty("url");            username = props.getProperty("username");            password = props.getProperty("password");        } catch (Exception ex) {            ex.printStackTrace();        }    }    public static Connection getConnection() {        Connection conn = null;        try {            Class.forName(driver);            conn = DriverManager.getConnection(url, username, password);        } catch (Exception ex) {            ex.printStackTrace();        }        return conn;    }    public static void release(Connection conn, PreparedStatement pstmt, ResultSet rs) {        if (rs != null) {            try {                rs.close();            } catch (Exception e) {                e.printStackTrace();            }        }        if (pstmt != null) {            try {                pstmt.close();            } catch (Exception e) {                e.printStackTrace();            }        }        if (conn != null) {            try {                conn.close();            } catch (Exception e) {                e.printStackTrace();            }        }    }}

View Code

 

Delete and modify data:

Package demo; import java. SQL. connection; import java. SQL. preparedStatement; import org. junit. test; public class TestUtils {/** Delete */@ Test public void testDelete () {Connection conn = null; PreparedStatement pstmt = null; try {conn = JDBCUtils3.getConnection (); string SQL = "delete from users where uid =? "; Pstmt = conn. prepareStatement (SQL); pstmt. setInt (1, 3); int row = pstmt.exe cuteUpdate (); if (row> 0) {System. out. println ("deleted successfully");} else {System. out. println ("failed to delete") ;}} catch (Exception e) {throw new RuntimeException (e) ;}finally {JDBCUtils3.release (conn, pstmt, null );}} /** modify */@ Test public void testUpdate () {Connection conn = null; PreparedStatement pstmt = null; try {conn = JDBC Utils3.getConnection (); String SQL = "update users set upassword =? Where uid =? "; Pstmt = conn. prepareStatement (SQL); pstmt. setString (1, "123456789"); pstmt. setInt (2, 2); int row = pstmt.exe cuteUpdate (); if (row> 0) {System. out. println ("modified successfully");} else {System. out. println ("failed to modify") ;}} catch (Exception e) {throw new RuntimeException (e) ;}finally {JDBCUtils3.release (conn, pstmt, null );}}}

View Code