JDBC应用实例

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

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

创建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导入到工程库中

最终运行结果