JDBC用ConnectionFactory创建数据库连接

墨蓝 2022-06-07 10:37 450阅读 0赞

数据库准备

![Center][]

一、创建jdbcinfo.properties配置文件

可以在文件内输入不同的数据库配置数据，这里以Sqlite数据为例

sqlite.driver=org.sqlite.JDBC  
sqlite.url=jdbc:sqlite:E:/SQLite3/StudentManageSystem.db  
sqlite.user=  
sqlite.password=

oracle.driver=oracle.jdbc.driver.OracleDriver  
oracle.url=jdbc:oracle:thin:@" + "host:port:databaseName  
oracle.user=\*\*  
oracle.password=\*\*

mysql.driver=com.mysql.jdbc.Driver  
mysql.url=jdbc:mysql://host:port:databaseName  
mysql.user=\*\*  
mysql.password=\*\*

二、创建ConnectionFactory类

package com.cjx913;
    
    import java.io.IOException;
    import java.io.InputStream;
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.SQLException;
    import java.util.Properties;
    
    public class ConnectionFactory {
    	private static String DRIVER;
    	private static String URL;
    	private static String USER;
    	private static String PASSWORD;
    	
    	static{
    		Properties properties = new Properties();
    		InputStream is = 
    				ConnectionFactory.class.getResourceAsStream("jdbcinfo.properties");
    		try {
    			properties.load(is);//加载配置文件
    			DRIVER = properties.getProperty("sqlite.driver");//读取文件配置数据库驱动
    			URL = properties.getProperty("sqlite.url");//读取文件配置数据库URL
    			USER = properties.getProperty("sqlite.user");//读取文件配置数据库用户
    			PASSWORD = properties.getProperty("sqlite.password");//读取文件配置数据库用户密码
    		} catch (IOException e) {
    			// TODO Auto-generated catch block
    			e.printStackTrace();
    		}
    		
    	}
    	
    	/**
    	 * 提供getConnection()方法
    	 * @return Connection
    	 */
    	public static Connection getConnection(){
    		Connection conn = null;
    		try {
    			Class.forName(DRIVER);
    			conn = DriverManager.getConnection(URL);
    		} catch (ClassNotFoundException e) {
    			e.printStackTrace();
    		} catch (SQLException e) {
    			e.printStackTrace();
    		}
    		
    		return conn;
    	}
    
    }

三、创建Student类

package com.cjx913;
    
    public class Student {
    
    	private int id;
    	private String name;
    	private String stu_class;
    
    	public Student() {
    		super();
    	}
    
    	public Student(int id, String name,String stu_class) {
    		super();
    		this.id = id;
    		this.name = name;
    		this.stu_class = stu_class;
    	}
    
    	public String getStu_class() {
    		return stu_class;
    	}
    
    	public void setStu_class(String stu_class) {
    		this.stu_class = stu_class;
    	}
    
    	public int getId() {
    		return id;
    	}
    
    	public void setId(int id) {
    		this.id = id;
    	}
    
    	public String getName() {
    		return name;
    	}
    
    	public void setName(String name) {
    		this.name = name;
    	}
    
    	@Override
    	public String toString() {
    		return "Student [id=" + id + ", name=" + name +", class=" + stu_class + "]";
    	}
    
    }

四、读取数据库测试类Test

package com.cjx913;
    
    import java.sql.Connection;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.util.ArrayList;
    import java.util.List;
    
    public class test {
    
    	public static void main(String[] args) {
    		List<Student> list = fecthData();
    
    		for (Student s : list) {
    			System.out.println(s);
    		}
    		
    		
    		insertData(13, "z", "6");
    		
    
    	}
    
    	private static void insertData(int id,String name,String stu_class){
    		Connection conn = null;
    		PreparedStatement ps = null;
    		boolean isExist = false;
    		conn = ConnectionFactory.getConnection();
    		try {
    			ps = conn.prepareStatement("SELECT id FROM Student");
    			ResultSet rs = ps.executeQuery();
    			while(rs.next()){
    				if(rs.getInt(1)==id){
    					isExist = true;
    				
    				}
    			}
    			if(!isExist){
    				ps = conn.prepareStatement("INSERT INTO Student VALUES(?,?,?)");
    				ps.setInt(1, id);
    				ps.setString(2, name);
    				ps.setString(3, stu_class);
    				ps.executeUpdate();
    				System.out.println("Insert Succeed!");
    			}
    		} catch (SQLException e) {
    			try {
    				conn.rollback();
    			} catch (SQLException e1) {
    				// TODO Auto-generated catch block
    				e1.printStackTrace();
    			}
    			e.printStackTrace();
    		}finally{
    			if (conn != null) {
    				try {
    					conn.close();
    				} catch (SQLException e) {
    					// TODO Auto-generated catch block
    					e.printStackTrace();
    				}
    			}
    		}
    	}
    	
    	private static List<Student> fecthData() {
    		Student student = null;
    		List<Student> list = new ArrayList<Student>();
    		Connection conn = ConnectionFactory.getConnection();
    		try {
    			
    			PreparedStatement ps = conn.prepareStatement("SELECT * FROM Student");
    			ResultSet rs = ps.executeQuery();
    			while (rs.next()) {
    				student = new Student();
    				student.setId(rs.getInt(1));
    				student.setName(rs.getString(2));
    				student.setStu_class(rs.getString(3));
    				list.add(student);
    			}
    		} catch (SQLException e) {
    			// TODO Auto-generated catch block
    			e.printStackTrace();
    		} finally {
    			if (conn != null) {
    				try {
    					conn.close();
    				} catch (SQLException e) {
    					// TODO Auto-generated catch block
    					e.printStackTrace();
    				}
    			}
    		}
    		return list;
    	}
    
    }

[Center]: /images/20220607/d538bac30c3f423783666ebca44bc64c.png