mysql--JDBC实例

1.从数据库读取相应的对象信息，给对象设置属性值，并存入到对象集合中
2.从集合读取对象信息，并写入数据库。

Student类

package com.situ.JDBCwork;

import java.time.LocalDate;

/**
* @author 作者：一只小海猪
* @version 创建时间：2021年11月9日 下午8:42:38
* 类说明
*/
public class Student {
	private int id ;
	private String name;
	private String sex;
	private LocalDate birthday;
	private String classname;
	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;
	}
	public String getSex() {
		return sex;
	}
	public void setSex(String sex) {
		this.sex = sex;
	}
	public LocalDate getBirthday() {
		return birthday;
	}
	public void setBirthday(LocalDate birthday) {
		this.birthday = birthday;
	}
	public String getClassname() {
		return classname;
	}
	public void setClassname(String classname) {
		this.classname = classname;
	}
	//测试时使用，用于查看对象是否正确赋值
	@Override
	public String toString() {
		return "Student [id=" + id + ", name=" + name + ", sex=" + sex + ", birthday=" + birthday + ", classname="
				+ classname + "]";
	}
	
}

Statement 的实现

package com.situ.JDBCwork;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.time.LocalDate;
import java.util.ArrayList;


/**
* @author 作者：一只小海猪
* @version 创建时间：2021年11月9日 下午8:42:03
* 类说明
*/
//使用statement的数据库查询
public class JdbcFromSQL {
	
	ArrayList<Student> slist = new ArrayList<>(); 
	
	//从数据库读取数据
	public void readFromSQL() {
		// TODO Auto-generated method stub
		Statement st = Jdbc();
		try {
			ResultSet rs1 = st.executeQuery("select count(sid) from t_student ");
			while(rs1.next()) {
			System.out.println("共有中学生："+rs1.getInt(1)+"人");
			}
		} catch (SQLException e1) {
			e1.printStackTrace();
		}
		
		try {
			ResultSet rs = st.executeQuery("select * from t_student");
			while(rs.next()) {
				Student s = new Student();
				s.setId(rs.getInt("sid"));
				s.setName(rs.getString("sname"));
				s.setSex(rs.getString("ssex"));
				s.setClassname(rs.getString("classname"));
				s.setBirthday(rs.getDate("sbirthday").toLocalDate());//date转localdate
				slist.add(s);
				System.out.println("成功读取 "+s);
			}
			System.out.println("数据读取成功！");
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			try {
				st.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
	}
	
	//存储对象的信息到数据库，使用statement ,这里仅仅是测试，没有加判断数据是否相同，如果要加，仅需要判断学生的学号是否与数据库相同就行
	public void saveToSQL() {
		int flag = 0 ;
		for(Student s: slist) {
		Statement st = Jdbc();
		int id = s.getId();
		String name = s.getName();
		String sex = s.getSex();
		LocalDate  birthday =s.getBirthday();
		String classname = s.getClassname();
		String sql="insert into t_student (sid,sname,ssex,sbirthday,classname) "
				+ "values("+id+","+"'"+name+"'"+","+"'"+sex+"'"+","+"'"+birthday+"'"+","+"'"+classname+"'"+")";
		System.out.println(sql);
        try {
			st.executeUpdate(sql);
			flag++;
		} catch (SQLException e) {
			System.out.println("存入数据库失败！");
			e.printStackTrace();
		}finally {
			try {
				st.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	  }
		System.out.println("存入数据库"+flag+"条数据！");
	}
	
	//私有方法连接数据库，返回一个statement对象
	private Statement Jdbc() {
		Statement statement = null;
		String url = "jdbc:mysql://localhost:3306/student?serverTimezone=Hongkong&useUnicode=true&characterEncoding=utf8&useSSL=false&autoReconnect=true ";
		String username = "root";
		String password = "123456";
		
		try {
			Class.forName("com.mysql.cj.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			System.out.println("驱动加载失败！");
			e.printStackTrace();
		}
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(url, username, password);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			statement = conn.createStatement();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		System.out.println("数据库连接成功！");
		return statement;
	}
}

PreperedStatement 的实现

package com.situ.JDBCwork;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

/**
* @author 作者：一只小海猪
* @version 创建时间：2021年11月9日 下午8:42:24
* 类说明
*/
public class JdbcFromMysql {
	ArrayList<Student> slist= new ArrayList<>();
	
	//从数据库读取信息，并作为对象存入学生集合
	public void readFromSQL() {
		Connection conn = jdbc();
			if(conn!=null) {
			String sql = "select * from t_student";
			try {
				PreparedStatement ps = conn.prepareStatement(sql);
				
				ResultSet rs = ps.executeQuery();
				while(rs.next()) {
					Student s = new Student();
					s.setId(rs.getInt("sid"));
					s.setName(rs.getString("sname"));
					s.setSex(rs.getString("ssex"));
					s.setClassname(rs.getString("classname"));
					s.setBirthday(rs.getDate("sbirthday").toLocalDate());//date转localdate
					slist.add(s);
					System.out.println("成功读取 "+s);
				}
				System.out.println("数据加载成功！");
				ps.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}finally {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			}else {
				System.out.println("数据库连接失败！");
			}
		
	}
	
	//私有方法连接数据库，外部不可用
	private Connection jdbc() {
		Connection conn = null;
		String url = "jdbc:mysql://localhost:3306/student?serverTimezone=Hongkong&useUnicode=true&characterEncoding=utf8&useSSL=false&autoReconnect=true ";
		String user = "root";
		String password = "123456";
		

			try {
				Class.forName("com.mysql.cj.jdbc.Driver");
				System.out.println("驱动加载成功！");
				
				conn = DriverManager.getConnection(url, user, password);
				System.out.println("数据库连接成功！");
				
			} catch (ClassNotFoundException e) {
				System.out.println("驱动加载失败！");
				e.printStackTrace();
			} catch (SQLException e) {
				System.out.println("数据库连接失败！");
				e.printStackTrace();
			}
		return conn;

	}
	
	//将对象的数据存入数据库
	public void saveToSQL() {
		Connection conn = jdbc();
		if(conn!=null) {
		try {
			for(Student s : slist) {
			String sql = "insert into t_student( sid ,sname , ssex, sbirthday,classname) "
				+ "values(?,?,?,?,?)";
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setInt(1, s.getId());
			ps.setString(2, s.getName());
			ps.setString(3,s.getSex());
			ps.setString(4,s.getBirthday().toString());
			ps.setString(5,s.getClassname());
			
			ps.executeUpdate();
			ps.close();
			}
			System.out.println("数据写入数据库成功！");
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
		}
	}
  }
	
	
	
	
	
	
}

测试主函数

package com.situ.JDBCwork;

/**
* @author 作者：一只小海猪
* @version 创建时间：2021年11月9日 下午8:42:31
* 类说明
*/
public class Test {

	public static void main(String[] args) {
		//statement的jdbc
		JdbcFromSQL jfs = new JdbcFromSQL();
		jfs.readFromSQL();
		jfs.saveToSQL();
		
		//preparedStatement的jdbc
		JdbcFromMysql jfm =new JdbcFromMysql();
		jfm.readFromSQL();
		jfm.saveToSQL();
	}

}