JDBC高级版本（JDBCVersion4）

最新推荐文章于 2024-07-09 14:55:57 发布

我已不帅了

最新推荐文章于 2024-07-09 14:55:57 发布

阅读量656

点赞数

分类专栏： JDBC高级（JDBCVersion4）文章标签： jdbc连接数据库 jdbc java mysql jdbc教程

本文链接：https://blog.csdn.net/tanmin_tm/article/details/52738742

版权

JDBC高级（JDBCVersion4）专栏收录该内容

1 篇文章 0 订阅

订阅专栏

JDBC高级版本（JDBCVersion4）

一：JAVA工程

二：数据库

<1>数据库和表

<2>数据库表结构

三：实体类

package cn.gosn.jdbcversion4.entity;
/**
 * 实体类
 * @author Administrator
 *
 */
public class Student {
	private int id;
	private String stu_name;
	private int stu_age;
	private String stu_gender;
	
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getStu_name() {
		return stu_name;
	}
	public void setStu_name(String stu_name) {
		this.stu_name = stu_name;
	}
	public int getStu_age() {
		return stu_age;
	}
	public void setStu_age(int stu_age) {
		this.stu_age = stu_age;
	}
	public String getStu_gender() {
		return stu_gender;
	}
	public void setStu_gender(String stu_gender) {
		this.stu_gender = stu_gender;
	}
	public Student(int id, String stu_name, int stu_age, String stu_gender) {
		super();
		this.id = id;
		this.stu_name = stu_name;
		this.stu_age = stu_age;
		this.stu_gender = stu_gender;
	}
	public Student() {
		super();
	}
	
}

四：JDBC类

package cn.gson.jdbcversion4;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Jdbc简单封装
 * @author Administrator
 *
 */
public class Jdbc {
	
	private static final String DRIVER = "com.mysql.jdbc.Driver";
	private static final String URL = "jdbc:mysql://localhost:3306/test";
	private static final String USERNAME = "root";
	private static final String PASSWORD = "1234";
	private static Jdbc instance;
	private static Connection conn;
	private static PreparedStatement pt;
	private static ResultSet rs;
	//1.加载驱动
	public Jdbc() {
		try {
			Class.forName(DRIVER);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}
	
	public static Jdbc getInstance(){
		if(instance == null){
			instance = new Jdbc();
		}
		return instance;
	}
	//2.创建连接
	private void createConnection(){
		try {
			if(conn == null || conn.isClosed()){
				conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * 执行所有的增，删，改
	 * @throws SQLException 
	 */
	public boolean executeUpdate(String sql,Object...objects) throws SQLException{
		this.creatPraparedStatement(sql,objects);
		
		boolean result = pt.executeUpdate()>0;
		this.closeConnection();
		return result;
	}
	/**
	 * 执行查询一条记录
	 */
	public Map
    
    
     
      executeQuery(String sql,Object...objects){
		this.creatPraparedStatement(sql, objects);
		Map
     
     
      
       row =null;
		try {
			rs = pt.executeQuery();
			while(rs.next()){
				row = new HashMap();
				ResultSetMetaData rm= rs.getMetaData();
			for (int i = 1; i <= rm.getColumnCount(); i++) {
				String name = rm.getColumnName(i);
				row.put(name, rs.getObject(name));
			}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return row;
	}
	/**
	 * 执行查询多条记录
	 * @throws SQLException 
	 */
	public List
      
      
       
       
        
        > executeQueryList(String sql,Object...objects) throws SQLException{
		this.creatPraparedStatement(sql, objects);
			rs = pt.executeQuery();
			List
        
         
         
           > rows = new ArrayList<>(); Map 
          
            row =null; ResultSetMetaData rm = rs.getMetaData(); while(rs.next()){ row = new HashMap(); for (int i = 1; i <=rm.getColumnCount() ; i++) { String name = rm.getColumnName(i); row.put(name, rs.getObject(name)); } rows.add(row); } return rows; } /** * 参数绑定 * @param sql * @param objects */ private void creatPraparedStatement(String sql, Object...objects) { this.createConnection(); try { pt = this.conn.prepareStatement(sql); if(objects != null && objects.length>0){ for (int i = 1; i <= objects.length; i++) { pt.setObject(i,objects[i-1]); } } } catch (SQLException e) { e.printStackTrace(); } } /** * 释放资源 */ private void closeConnection(){ if(rs != null){ try { rs.close(); rs = null; } catch (SQLException e) { e.printStackTrace(); } } if(pt != null){ try { pt.close(); pt = null; } catch (SQLException e) { e.printStackTrace(); } } if(conn != null){ try { conn.close(); conn = null; } catch (SQLException e) { e.printStackTrace(); } } } }

五：Dao类

package cn.gson.jdbcversion4.dao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import cn.gosn.jdbcversion4.entity.Student;
import cn.gson.jdbcversion4.Jdbc;

/**
 * dao类
 * @author Administrator
 *
 */
public class StudentDao {
	
	/**
	 * 新增学生方法
	 * @throws SQLException 
	 */
	public static boolean addStudent(Student student) throws SQLException{
		String sql = "insert into student (stu_name,stu_age,stu_gender) values(?,?,?);";
		return Jdbc.getInstance().executeUpdate(sql, student.getStu_name(),student.getStu_age(),student.getStu_gender());
	}
	
	/**
	 * 修改学生方法
	 * @throws SQLException 
	 */
	public static boolean updateStudent(int stuAge,String stuName) throws SQLException{
		String sql = "update student set stu_age=? where stu_name=?";
		return Jdbc.getInstance().executeUpdate(sql,stuAge,stuName);
	}
	
	/**
	 * 删除学生方法
	 * @throws SQLException 
	 */
	public static boolean deleteStudent(String stuName) throws SQLException{
		String sql = "delete from student where stu_name=?";
		return Jdbc.getInstance().executeUpdate(sql, stuName);
	}
	
	/**
	 * 根据id查询一条学生信息
	 */
	public static Student selectStudent(int id){
		String sql = "select *from student where id =?";
		Map
    
    
     
      map = Jdbc.getInstance().executeQuery(sql, id);
		return builder(map);
	}
	/**
	 * 查询所有学生信息
	 * @throws SQLException 
	 */
	public static List
     
     
      
       selectAllStudent() throws SQLException{
		String sql = "select * from student";
		List
      
      
       
       
        
        > list = Jdbc.getInstance().executeQueryList(sql);
		List
        
        
          studentList = new ArrayList 
         
           (); for (Map 
          
            map : list) { studentList.add(builder(map)); } return studentList; } /** *分页查询学生信息 */ private static Integer pageSize = 2;//每一页显示记录条数 public static List 
           
             selectPageStudent(Integer page) throws SQLException{ Integer start = (page-1) * pageSize;//每一页的起始页码 String sql = "select * from student order by id desc limit ?,?"; List 
             
             
               > list = Jdbc.getInstance().executeQueryList(sql,start,pageSize); List 
              
                studentList = new ArrayList 
               
                 (); for (Map 
                
                  map : list) { studentList.add(builder(map)); } return studentList; } /** * 总页数 */ public static Integer totalPageStudent(){ String sql = "select count(*) total from student"; Map 
                 
                   map = Jdbc.getInstance().executeQuery(sql); Integer total = Integer.parseInt(map.get("total").toString()); return (int)Math.ceil(total.doubleValue()/pageSize); } /** * map转化为对象 * @param map */ private static Student builder(Map 
                  
                    map) { Student student = new Student(); student.setId(Integer.parseInt(map.get("id").toString())); student.setStu_name(map.get("stu_name").toString()); student.setStu_age(Integer.parseInt(map.get("stu_age").toString())); student.setStu_gender(map.get("stu_gender").toString()); return student; } }

六：Test类

package cn.gson.jdbcversion4.controller;

import java.sql.SQLException;
import java.util.List;

import cn.gosn.jdbcversion4.entity.Student;
import cn.gson.jdbcversion4.dao.StudentDao;

/**
 * 测试类
 * @author Administrator
 *
 */
public class Test {
	public static void main(String[] args) throws SQLException {
		//新增
		Student student = new Student();
		student.setStu_name("小王");
		student.setStu_age(20);
		student.setStu_gender("男");
		StudentDao.addStudent(student);
		//修改
		int stuAge = 50;
		String stuName = "小王";
		StudentDao.updateStudent(stuAge, stuName);
		//删除
		StudentDao.deleteStudent(stuName);
		//查询一条记录
		student = StudentDao.selectStudent(16);
		System.out.println("id"+"\t"+"姓名"+"\t"+"年龄"+"\t"+"性别");
		System.out.println(student.getId()+"\t"+student.getStu_name()+"\t"+student.getStu_age()+"\t"+student.getStu_gender());
		//查询多条记录
		List
   
   
    
     list = StudentDao.selectAllStudent();
		System.out.println("id"+"\t"+"姓名"+"\t"+"年龄"+"\t"+"性别");
		for (Student student2 : list) {
			System.out.println(student2.getId()+"\t"+student2.getStu_name()+"\t"+student2.getStu_age()+"\t"+student2.getStu_gender());

		}
		//查询总页数
		System.out.println("总页数："+StudentDao.totalPageStudent());
		//分页查询
		List
    
    
     
      list1 = StudentDao.selectPageStudent(1);
		System.out.println("id"+"\t"+"姓名"+"\t"+"年龄"+"\t"+"性别");
		for (Student student2 : list1) {
			System.out.println(student2.getId()+"\t"+student2.getStu_name()+"\t"+student2.getStu_age()+"\t"+student2.getStu_gender());

		}
		/*		
		一般分页的格式为：当前页/总页数,首页,上一页,下一页,尾页,原理就是根据当前的页数查出当前页的数据并显示，一般表现为按钮或超链接到后台取数据
		当前页/总页数：page/totalPage
		首先判断当前页是否大于1，大于1才有分页
		首页：如果当前页大于1，则page=1
		上一页：如果当前页大于1，则page-1
		下一页：如果当前页大于1，则page+1
		尾页：如果当前页大于1，则page=totalPage
		*/
	}
}