2020-12-18

数据库的连接封装

1 jdbcutils的编写

用于得到自己数据库的连接

package com.usst.xqs.pojo;

import com.mysql.jdbc.Connection;
import com.usst.xqs.Controllor.StudentDao;

import java.sql.DriverManager;
import java.sql.SQLException;

public class JdbcUtil {
    private static String url="jdbc:mysql://localhost:3306/test1";
    private static String username="root";
    private static  String password="123456";
    private static Connection conn=null;
    public static Connection getConn() {//得到连接
        try {//加载驱动
            Class.forName("com.mysql.jdbc.Driver");
            conn= (Connection) DriverManager.getConnection(url, username, password);
        }catch (Exception e){
            e.printStackTrace();
        }

        return conn;
    }
   public void close() throws SQLException {//关闭连接
        conn.close();
   }
}

     1.url="jdbc:mysql://localhost:3306/test1"   本地数据库下的test1数据库的地址
     2.username  自己下载数据库时设置的连接名字 默认root
     3.password  设置的密码    默认密码我忘记    应该是123456（可以查一下）

2 测试数据类

用于此次Dao的数据测试对象（可以自行改写）

package com.usst.xqs.pojo;

public class Student {
    private String name;
    private String id;
    private  String address;

    public Student() {
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getId() {
        return id;
    }

    public void setId(String id) {
        this.id = id;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    @Override
    public String toString() {
        return "Student{" +
                "name='" + name + '\'' +
                ", id='" + id + '\'' +
                ", address='" + address + '\'' +
                '}';
    }
}

3.studentDao的编写

package com.usst.xqs.Controllor;

import com.usst.xqs.pojo.JdbcUtil;
import com.usst.xqs.pojo.Student;

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

public class StudentDao {
    public static List<Student> getAllStudent()  {//查询所有学生信息
        //创建学生列表
        List<Student> students=new ArrayList<Student>();
        //得到连接
        Connection connection= JdbcUtil.getConn();
        //sql语句
        String sql="select *from student";
        try {
            //得到查询的结果
            PreparedStatement preparedStatement=connection.prepareStatement(sql);
            ResultSet resultSet=preparedStatement.executeQuery();
            //遍历查询结果
            while (resultSet.next()){
                //将数据封装到list<student>中
                Student student=new Student();
                student.setName(resultSet.getString("name"));
                student.setId(resultSet.getString("id"));
                student.setAddress(resultSet.getString("address"));
                students.add(student);
            }
            //关闭连接
            preparedStatement.close();
            resultSet.close();
            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return students;
    }

    public static boolean addStudent(String sql,Student student){//插入学生信息
        //sql语句  此处的语句只是为了展示后面的占位符替换
        sql="insert into student values(?,?,?)";
        //得到连接
        Connection connection=JdbcUtil.getConn();
        try {
            //替换sql语句中的占位符
            PreparedStatement preparedStatement=connection.prepareStatement(sql);
            preparedStatement.setString(1,student.getName());
            preparedStatement.setString(2,student.getId());
            preparedStatement.setString(3,student.getAddress());
            //执行语句
            int count=preparedStatement.executeUpdate();
            //关闭连接
            connection.close();
            preparedStatement.close();
            //返回成功与否
            if (count>0)
                return true;
            else return false;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    public static boolean updateStudent(String sql,Student student,String name,String id){//通过name和id查询更新
        //sql语句此处的语句只是为了展示后面的占位符替换
         sql="update student set name=?,id=?,address=? where name =? and id=?";
        //得到连接
        Connection connection=JdbcUtil.getConn();
        try {
            //替换sql语句中的占位符
            PreparedStatement preparedStatement=connection.prepareStatement(sql);
            preparedStatement.setString(1,student.getName());
            preparedStatement.setString(2,student.getId());
            preparedStatement.setString(3,student.getAddress());
            preparedStatement.setString(4,name);
            preparedStatement.setString(5,id);
            //执行语句
            int count=preparedStatement.executeUpdate();
            //关闭连接
            connection.close();
            preparedStatement.close();
            //返回成功与否
            if (count>0)
                return true;
            else
                return false;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;

    }
    public static boolean deleteStudent(String sql,String name,String id){//通过name和id查询删除
        //sql语句 此处的语句只是为了展示后面的占位符替换
         sql="delete from student where name =? and id=?";
        //得到连接
        Connection connection=JdbcUtil.getConn();
        try {
            //替换sql语句中的占位符
            PreparedStatement preparedStatement=connection.prepareStatement(sql);
            preparedStatement.setString(1,name);
            preparedStatement.setString(2,id);
            //执行语句
            int count=preparedStatement.executeUpdate();
            //关闭连接
            connection.close();
            preparedStatement.close();
            //返回成功与否
            if (count>0)
                return true;
            else
                return false;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }
}

以上代码自己测试成功，数据库中的字典如下

name	id	address
不知	不知	不知