データベースMySQLベース---JDBC開発手順--JDBCパッケージツールクラス--PreparedStatement CRUD操作を実現

52901 ワード

データベースMySQL データベース jdbc

JDBCの概要

1、JDBC  
	Java     （Java Database Connectivity，  JDBC）：
	 Java                           ，                   。
	JDBC  Sun Microsystems   。      JDBC          。
	JDBC  ：
	 sun             （    ）。                ，                ，
	           。

2、    
            
       SQL   
        

3、JDBC      
    DriverManager ：     ，    ，  JDBC        
    Connection  ：    ，    ，         
    Statement  ：SQL   ，    ，      SQL  ，      
    ResultSet  ：     ，    ，       ，    
    
4、mysql      ：https://dev.mysql.com/downloads/connector/j/

JDBCの開発手順

1、    jar ，        
2、  jdbc  ：
	(1)、     
    (2)、           
    (3)、    sql     (Statement PreparedStatement)   SQL    
    (4)、  SQL  
    (5)、  sql   
    executeQuery()-->       -->        ResultSet
	executeUpdate()-->        -->           int
    (6)、  sql       
    (7)、

完全なコード

package com.offcn.demo;
import java sql.Connection;
import java. sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class JDBCDetail {
	public static void main(String[] args) {
		Connnection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            //    
            Class.forName("com.mysql.cj.jdbc.Driver");
            //    
            String url = "jdbc:mysql://localhost:3306/db01?serverTimezone=GMT%2B8&characterEncoding=UTF-8";
            String username = "root";
            String passward = "000";
            conn = DriverManager.getConnection(url,username,passward);
            //  SQL   
            stmt = conn.createStatement();
            //  SQL  
            String sql = "select sname,sage from student";
            //  SQL       
            rs = stmt.executeQuery(sql);
            //     
            while(rs.next()){
                //      
                //   ：                    
                System.out.println(rs.getString("sname")+rs.getInt("sage"));
                //   ;                      
                System.out.println(rs.getString(1)+rs.getInt(2));
            }
        }catch(Evception e) {
            e.printStaclTrace();
        }finally {
            try {
                //    ，    
                if(rs!=null) {
                    rs.close();
                }
                if(stmt!=null) {
                    stmt.close();
                }
                if(conn!=null) {
                    conn.close();
                }
            } catch(SQLException e) {
                e.printStackTrace();
            }
        }
	}
}

ユニットテスト

       jar 
    -->Build Path-->Configure Build Path-->Libraries-->Add Libraries-->JUnit-->Next-->Apply and Close

SQL注入の問題

       ：
public void select(String sname) {
    ...
    String sql = "select * from student where saname = '" + sname + "'";
}
      ：
    String sname = "999' or '1'='1";
      SQL   ：
    select * from student where sname='999' or '1'='1'
where         ，          ，       
  SQL      Statement  ，     PreparedStatement

JDBCパッケージツールクラス
srcディレクトリにjdbc.propertiesファイルを作成するには、次の手順に従います.

  properties       。     key=value      。Java     Properties          String value=p.getProperty(key);

      ：
driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/school?serverTimezone=GMT%2B8&characterEncoding=UTF-8
user=root
pwd=000

JDBCUtil.javaファイルを作成するには、次の手順に従います.

package com.offcn.demo;
import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;
public class JDBCUtil {
	static String driver = "";
	static String url = "";
	static String user = "";
	static String pwd = "";
	static {
		try {
			InputStream in = new FileInputStream("src/jdbc.properties");
			Properties prop = new Properties();
			//               
			prop.load(in);
			//  key  value
			driver = prop.getProperty("driver");
			url = prop.getProperty("url");
			user = prop.getProperty("user");
			pwd = prop.getProperty("pwd");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	//         
	public static Connection getConn() {
		Connection conn = null;
		try {
			Class.forName(driver);
			conn = DriverManager.getConnection(url, user, pwd);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return conn;
	}
	//         
	public static void closeResources(ResultSet rs,PreparedStatement pstmt,Connection conn) {
		try {
			if(rs!=null) {
				rs.close();
			}
			if(pstmt!=null) {
				pstmt.close();
			}
			if(conn!=null) {
				conn.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}

PreparedStatementはCRUD操作を実現

  SQLyog   school   ，    student
CREATE DATABASE school;
USE school;
CREATE TABLE student(
sid INT(6) PRIMARY KEY  AUTO_INCREMENT,
sname VARCHAR(20) NOT NULL,
sage INT(3),
ssex CHAR(1)  DEFAULT  ' ',
semail VARCHAR(20) UNIQUE
);

package com.ujiuye;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.junit.jupiter.api.Test;

public class CRUD {
	//                
	@Test
	public void makeTable() {
		insert("   ",23," ","[email protected]");
		insert("   ",22," ","[email protected]");
		insert("   ",21," ","[email protected]");
		insert("   ",20," ","[email protected]");
		insert("   ",19," ","[email protected]");
		insert("   ",18," ","[email protected]");
		insert("    ",17," ","[email protected]");
		insert("  ",30," ","[email protected]");
		insert("   ",25," ","[email protected]");
		insert("   ",18," ","[email protected]");
	}

	//                    
	@Test
	public void remove() {
		int sid = 2;
		delete(sid);
	}
	//                
	@Test
	public void change() {
		update(10,"   ",28," ","[email protected]");
	}
	//          
	public void insert(String sname,int sage,String ssex,String semail) {
		Connection conn = JDBCUtil.getConn();
		PreparedStatement pstmt = null;
		String sql = "insert into student(sname,sage,ssex,semail) values(?,?,?,?) ";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, sname);
			pstmt.setInt(2, sage);
			pstmt.setString(3, ssex);
			pstmt.setString(4, semail);
			int rows = pstmt.executeUpdate();
			System.out.println(rows);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.closeResources(null, pstmt, conn);
		}
	}
	//                  
	public void delete(int sid) {
		Connection conn = JDBCUtil.getConn();
		PreparedStatement pstmt = null;
		String sql = "delete from student where sid=? ";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, sid);
			int rows = pstmt.executeUpdate();
			System.out.println(rows);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.closeResources(null, pstmt, conn);
		}
	}
	//              
	public void update(int sid,String sname,int sage,String ssex,String semail) {
		Connection conn = JDBCUtil.getConn();
		PreparedStatement pstmt = null;
		String sql = "update student set sname=?,sage=?,ssex=?,semail=? where sid=?";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, sname);
			pstmt.setInt(2, sage);
			pstmt.setString(3, ssex);
			pstmt.setString(4, semail);
			pstmt.setInt(5, sid);
			int rows = pstmt.executeUpdate();
			System.out.println(rows);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.closeResources(null, pstmt, conn);
		}
	}
	
	//             ，       
	@Test
	public void select1() {
		Connection conn = JDBCUtil.getConn();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "select * from student where ssex=' '";
		try {
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			while(rs.next()) {
				System.out.println(
						"  ："+rs.getInt("sid")+
						"    ："+rs.getString("sname")+
				        "    ："+rs.getInt("sage")+
				        "    ："+rs.getString("ssex")+
				        "    ："+rs.getString("semail"));
				}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.closeResources(rs, pstmt, conn);
		}
	}
	//         21        ，       
	@Test
	public void select2() {
		Connection conn = JDBCUtil.getConn();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "select * from student where ssex=' ' and sage>21";
		try {
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			while(rs.next()) {
				System.out.println(
						"  ："+rs.getInt("sid")+
						"    ："+rs.getString("sname")+
				        "    ："+rs.getInt("sage")+
				        "    ："+rs.getString("ssex")+
				        "    ："+rs.getString("semail"));
				}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.closeResources(rs, pstmt, conn);
		}
	}
}

Effective java第2版-ノート(01)単例(Singleton)の列挙(enum)実現

HDU 3833 YY's new problem (hash)