jdbc的简单使用

1.数据库表格

2.创建表格所对应的类

 

package j2se;

public class Grade {
	private int id;
	private String name;
	private String manager;
	private String email;
	private int telephone;
	private String introducation;
	public  void setId(int id)
	{
		this.id=id;
	}
	public int getId(){
		return id;
	}
	public void setName(String name){
		this.name=name;
	}
	public String getName(){
		return name;
	}
}

3.在主函数中进行增、删、改、查

package j2se;

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;
/*
建立JDBC的连接
*/
public class JDBC {
public static Connection getConnection(){
	Connection conn = null;
	 try {
		 	//初始化驱动类com.mysql.jdbc.Driver
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/zhxy_db?characterEncoding=UTF-8","root", "root");
            //该类就在 mysql-connector-java-5.0.8-bin.jar中,如果忘记了第一个步骤的导包，就会抛出ClassNotFoundException
        } catch (ClassNotFoundException e) { 				
            e.printStackTrace();
        }catch (SQLException e) {							
            e.printStackTrace();
        }
	 return conn;
}
/*
    添加数据
*/
public static void addCourse(String name){
String sql = "insert into tb_grade(name) values(?)";  
//该语句为每个 IN 参数保留一个问号（“？”）作为占位符
Connection conn = null;				//和数据库取得连接
PreparedStatement pstmt = null;		//创建statement
try{
	conn = getConnection();
	pstmt = (PreparedStatement) conn.prepareStatement(sql);
	pstmt.setString(1, name); //给占位符赋值
	pstmt.executeUpdate();			//执行
}catch(SQLException e){
	e.printStackTrace();
}
}
public static void delCourse(int courseId){
String sql = "delete from tb_grade where id = ?";
Connection conn = null;
PreparedStatement pstmt = null;
try {
	conn = getConnection();
	pstmt = (PreparedStatement) conn.prepareStatement(sql);
	pstmt.setInt(1, courseId);
	pstmt.executeUpdate();
} catch (SQLException e) {
	// TODO: handle exception
	e.printStackTrace();
}
}
public static void modifyCourse(int courseId,String courseName){
String sql = "update tb_grade set name =? where id=?";
Connection conn = null;
PreparedStatement pstmt = null;
try {
	conn = getConnection();
	pstmt = (PreparedStatement) conn.prepareStatement(sql);
	pstmt.setString(1, courseName);  //利用Preparedstatement的set方法给占位符赋值
	pstmt.setInt(2, courseId);
	pstmt.executeUpdate();
} catch (SQLException e) {
	// TODO: handle exception
	e.printStackTrace();
}
}
/*
查询数据
*/
public static List<Grade> findCourseList(){
String sql = "select * from tb_grade order by id";
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
//创建一个集合对象用来存放查询到的数据
List<Grade> gradeList = new ArrayList<>();
try {
	conn = getConnection();
	pstmt = (PreparedStatement) conn.prepareStatement(sql);
	rs = (ResultSet) pstmt.executeQuery();
	while (rs.next()){
		int courseId = rs.getInt("id");
		String courseName = rs.getString("name");
		//每个记录对应一个对象
		Grade grade = new Grade();
		grade.setId(courseId);
		grade.setName(courseName);
		//将对象放到集合中
		gradeList.add(grade);
	}
} catch (SQLException e) {
	// TODO: handle exception
	e.printStackTrace();
}
return gradeList;
}
/**
 * 封装三个关闭方法
 * @param pstmt
 */
public static void close(PreparedStatement pstmt){
	if(pstmt != null){						//避免出现空指针异常
		try{
			pstmt.close();
		}catch(SQLException e){
			e.printStackTrace();
		}
		
	}
}

public static void close(Connection conn){
	if(conn != null){
		try {
			conn.close();
		} catch (SQLException e) {
			// TODO: handle exception
			e.printStackTrace();
		}
	}
}

public static void close(ResultSet rs){
	if (rs != null) {
		try {
			rs.close();
		} catch (SQLException e) {
			// TODO: handle exception
			e.printStackTrace();
		}
	}
}
	public static void main(String[] args) {
		// TODO Auto-generated method stub
		Connection conn=getConnection();
		System.out.println(conn);
//		addCourse("七年级");
//		delCourse(5);
//		modifyCourse(13,"六年级");
		List<Grade> gradeList = new ArrayList<>();
		gradeList=findCourseList();
		for (Grade grade:gradeList){
			System.out.println(grade.getId());
			System.out.println(grade.getName());
			
		}
	}

}