使用原生JDBC操作数据库

Bertha 。 2022-10-18 00:54 293阅读 0赞

> 现在都是使用mybatis来操作数据库，至于如何使用JDBC来操作数据库，基本上忘记的差不多了，特此记录一下  
> 感情深不深 ，都在代码里，废话不多说，直接上代码

import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.SQLException;
    /** * 工具类，获取数据库的连接 */
    public class DBUtil { 
    	private static String URL="jdbc:mysql://127.0.0.1:3306/test";
    	private static String USER="root";
    	private static String PASSWROD ="123456";
    	private static Connection connection=null;
    	static{ 
    		try { 
    			Class.forName("com.mysql.jdbc.Driver");
    			// 获取数据库连接
    			connection=DriverManager.getConnection(URL,USER,PASSWROD);
    			System.out.println("连接成功");
    		} catch (ClassNotFoundException e) { 
    			// TODO Auto-generated catch block
    			e.printStackTrace();
    		} catch (SQLException e) { 
    			// TODO Auto-generated catch block
    			e.printStackTrace();
    		}
    	}
    	// 返回数据库连接
    	public static Connection getConnection(){ 
    		return connection;
    	}
    }

import model.Student;
    
    import java.util.ArrayList;
    import java.util.List;
    import java.sql.Connection;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Statement;
    
    import db.DBUtil;
    /** * 操作学生表的dao类 * 下面均使用预编译的方法 */
    public class StudentDao { 
    	//将连接定义为单例
    	private static Connection connection = DBUtil.getConnection();
    	// 添加新的学生
    	public void addStudent(Student student){ 
    		String sql ="insert into student(name,age,score) "+
    					"values(?,?,?)";
    		boolean result = false;
    		try { 
    			// 将sql传进去预编译
    			PreparedStatement preparedstatement = connection.prepareStatement(sql);
    			// 下面把参数传进去
    			preparedstatement.setString(1, student.getName());
    			preparedstatement.setInt(2, student.getAge());
    			preparedstatement.setDouble(3, student.getScore());
    			preparedstatement.execute();
    		} catch (SQLException e) { 
    			// TODO Auto-generated catch block
    			e.printStackTrace();
    			System.out.println("创建数据库连接失败");
    		}
    	}
    	// 更新学生信息
    	public void updateStudent(Student student){ 
    		String sql = "update student set name = ? ,age =?,score = ? where id = ? ";
    		boolean result = false;
    		try { 
    			PreparedStatement preparedStatement = connection.prepareStatement(sql);
    			preparedStatement.setString(1, student.getName());
    			preparedStatement.setInt(2, student.getAge());
    			preparedStatement.setDouble(3, student.getScore());
    			preparedStatement.setInt(4, student.getId());
    			preparedStatement.executeUpdate();
    		} catch (SQLException e) { 
    			// TODO Auto-generated catch block
    			e.printStackTrace();
    			System.out.println("连接数据库失败");
    		}
    	}
    	// 根据id删除一个学生
    	public void deleteStudent(int id){ 
    		String sql = "delete from student where id = ?";
    		boolean result = false;
    		try { 
    			PreparedStatement preparedStatement = connection.prepareStatement(sql);
    			preparedStatement.setInt(1, id);
    			result=preparedStatement.execute();
    		} catch (SQLException e) { 
    			// TODO Auto-generated catch block
    			e.printStackTrace();
    		}
    	}
    	// 根据id查询学生
    	public Student selectStudent(int id){ 
    		String sql ="select * from student where id =?";
    		try { 
    			PreparedStatement preparedStatement = connection.prepareStatement(sql);
    			preparedStatement.setInt(1, id);
    			ResultSet resultSet = preparedStatement.executeQuery();
    			Student student = new Student();
    			// 一条也只能使用resultset来接收
    			while(resultSet.next()){ 
    				student.setId(resultSet.getInt("id"));
    				student.setName(resultSet.getString("name"));
    				student.setAge(resultSet.getInt("age"));
    				student.setScore(resultSet.getDouble("score"));
    			}
    			return student;
    		} catch (SQLException e) { 
    			// TODO: handle exception
    		}
    		return null;
    	}
    	// 查询所有学生，返回List
    	public List<Student> selectStudentList(){ 
    		List<Student>students  = new ArrayList<Student>(); 
    		String sql ="select * from student ";
    		try { 
    			PreparedStatement preparedStatement = DBUtil.getConnection().prepareStatement(sql);
    			ResultSet resultSet = preparedStatement.executeQuery();
    			// 不能把student在循环外面创建，要不list里面六个对象都是一样的，都是最后一个的值，
    			// 因为list add进去的都是引用
    			// Student student = new Student();
    			while(resultSet.next()){ 
    				Student student = new Student();
    				student.setId(resultSet.getInt(1));
    				student.setName(resultSet.getString(2));
    				student.setAge(resultSet.getInt(3));
    				student.setScore(resultSet.getDouble(4));
    				students.add(student);
    
    			}
    		} catch (SQLException e) { 
    			// TODO: handle exception
    		}
    		return students;
    	}
    }``