JDBC应用实例

快来打我* 2022-06-07 08:42 225阅读 0赞

本次实例使用SQLite3数据库，JDBC驱动是sqlite-jdbc-3.14.2.jar

创建数据库StudentManageSystem.db，创建表Student

![Center][]

创建Student类

package cjx913;
    
    public class Student {
    
    	private int id;
    	private String name;
    
    	public Student() {
    		super();
    	}
    
    	public Student(int id, String name) {
    		super();
    		this.id = id;
    		this.name = name;
    	}
    
    	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 + "]";
    	}
    
    }

创建test类

package cjx913;
    
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Statement;
    import java.util.ArrayList;
    import java.util.List;
    
    public class test {
    	/**
    	 * 使用SQLite数据库
    	 */
    	private static String sqlDriver = "org.sqlite.JDBC" ;
    	private static Connection conn = null;
    	private static String url = "jdbc:sqlite:E:/SQLite3/StudentManageSystem.db";
    	
    	/**
    	 * 使用Oracle数据库
    	 * String sqlDriver = "oracle.jdbc.driver.OracleDriver" ;// 加载Oracle驱动程序
    	 * String url = "jdbc:oracle:thin:@" + "host:port:databaseName"; // 连接使用的url  
    	 * String user = "your database userName"; // 数据库用户名  
    	 * String password = "your database password"; // 密码  
    	 * conn = DriverManager.getConnection(url, user, password);// 获取连接  
    	 * 
    	 */
    	
    	/**
    	 * 使用MySQL数据库
    	 * String sqlDriver = "com.mysql.jdbc.Driver" ;
    	 * String url = "jdbc:mysql://host:port:databaseName";
    	 * String username = "your database userName";
    	 * String password = "your database password";
    	 */
    
    	public static void main(String[] args) {
    
    		List<Student> list = fetchData();
    		for(Student e:list){
    			System.out.println(e);
    		}
    		
    		Student stu = login(2,"b");
    		if(stu!=null){
    			System.out.println("logined succeed"+"\n当前登录的用户是："+stu.getName());
    		}else{
    			System.out.println("logined failed");
    		}
    		//System.out.println(stu);
    		
    		//register(6, "f");
    		//register(7, "g");
    	}
    
    	public static void register(int id,String name){
    		try {
    			Class.forName(sqlDriver);
    			conn = DriverManager.getConnection(url);//用Oracle数据库和MySQL数据库有用户和密码要输入
    			PreparedStatement ps = conn.prepareStatement("INSERT INTO Student VALUES(?,?)");
    			ps.setInt(1, id);
    			ps.setString(2, name);
    			ps.executeUpdate();
    		} catch (ClassNotFoundException e) {
    			e.printStackTrace();
    		} catch (SQLException e) {
    			e.printStackTrace();
    		}finally{
    			if (conn != null) {
    				try {
    					conn.close();
    				} catch (SQLException e) {
    					e.printStackTrace();
    				}
    			}
    		}
    		
    	}
    	public static Student login(int id,String name){
    		Student student = null;
    		try {
    			Class.forName(sqlDriver);
    			conn = DriverManager.getConnection(url);
    			PreparedStatement ps = conn.prepareStatement("SELECT * FROM Student WHERE id=? AND name=?");
    			ps.setInt(1, id);
    			ps.setString(2, name);
    			ResultSet rs = ps.executeQuery();
    			if(rs.next()){
    				student = new Student(rs.getInt(1), rs.getString(2));
    			}
    		} catch (ClassNotFoundException e) {
    			e.printStackTrace();
    		} catch (SQLException e) {
    			e.printStackTrace();
    		}finally{
    			if (conn != null) {
    				try {
    					conn.close();
    				} catch (SQLException e) {
    					e.printStackTrace();
    				}
    			}
    		}
    		
    		return student;
    	}
    	
    	public static List<Student> fetchData() {
    		List<Student> list = new ArrayList<Student>();
    		Student student = null;
    		try {
    			Class.forName(sqlDriver);
    			conn = DriverManager.getConnection(url);
    			Statement sm = conn.createStatement();
    			String sel = "SELECT * FROM Student";
    			ResultSet rs = sm.executeQuery(sel);
    			while (rs.next()) {
    				student = new Student(rs.getInt(1), rs.getString(2));
    				list.add(student);
    			}
    		} catch (ClassNotFoundException e) {
    			e.printStackTrace();
    		} catch (SQLException e) {
    			e.printStackTrace();
    		} finally {
    			if (conn != null) {
    				try {
    					conn.close();
    				} catch (SQLException e) {
    					e.printStackTrace();
    				}
    			}
    		}
    		return list;
    	}
    }

sqlite-jdbc-3.14.2.jar导入到工程库中

![Center 1][]

最终运行结果

![Center 2][]

[Center]: /images/20220607/a3bf6eb4b91a4e97986dcaaa12ade9af.png
[Center 1]: /images/20220607/cdba0deb92f14f7399f0bcd93143750b.png
[Center 2]: /images/20220607/301a9d311b634b24a87e6b365277ba1c.png