jdbc 连接 数据库 单表增删改查

//使用简单的java项目实现jdbc连接数据库完成增删改查

//1.建立一个简单的java项目   

2.导入jar包

在web/WEB-INF下创建一个lib包 在lib包中导入连接数据库的jar包

 

3.右击lib包点击 Add as Library   给导入的jar包编译

 

4.jdbc 连接数据库

package com.dhx.util;

import java.sql.*;

/**
 * @author dhx
 * @version 1.0.0
 * @ClassName BaseUtil.java
 * @Description TODO
 * @createTime 2022年04月26日 22:27:00
 */
public class BaseUtil {
    static {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            //Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    public static Connection getConn() throws SQLException {
        Connection conn = DriverManager.getConnection("jdbc:mysql:///e?useSSL=false&useUnicode=true&characterEncoding=utf8", "root", "root");
        return conn;
    }
    public static void guan(ResultSet rs, PreparedStatement ps,Connection conn) throws SQLException {
        if (rs!=null){
            rs.close();
        }
        if (ps!=null){
            ps.close();
        }
        if (conn!=null){
            conn.close();
        }
    }
}

 

　

5.添加entity实体类 用于映射表中字段

package com.dhx.entity;
//数据库的实体类映射
public class Emp {
    private  int id;
    private String name;
    private String pwd;
    private int age;

    @Override
    public String toString() {
        return "Emp{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", pwd='" + pwd + '\'' +
                ", age=" + age +
                '}';
    }

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

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

    public void setPwd(String pwd) {
        this.pwd = pwd;
    }

    public void setAge(int age) {
        this.age = age;
    }

    public int getId() {
        return id;
    }

    public String getName() {
        return name;
    }

    public String getPwd() {
        return pwd;
    }

    public int getAge() {
        return age;
    }

    public Emp(int id, String name, String pwd, int age) {
        this.id = id;
        this.name = name;
        this.pwd = pwd;
        this.age = age;
    }

    public Emp() {
    }
}

 

　　

6.连接数据库后使用jdbc写增删改查语句

package com.dhx.dao;

import com.dhx.entity.Emp;
import com.dhx.util.BaseUtil;

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 EmpDao {
    //jdbc 查询方法
    public static List<Emp> select() {
        ArrayList<Emp> list = new ArrayList<>();
        try {
            Connection conn = BaseUtil.getConn();
            PreparedStatement ps = conn.prepareStatement("select * from t_emp");
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                Emp emp = new Emp(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getInt(4));
                list.add(emp);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return list;
    }

    //jdbc添加方法
    public static int insert(String name, String pwd, String age) {
        try {
            Connection conn = BaseUtil.getConn();
            PreparedStatement ps = conn.prepareStatement("insert  into t_emp values (null ,?,?,?)");

            ps.setString(1, name);
            ps.setString(2, pwd);
            ps.setString(3, age);
            int i = ps.executeUpdate();
            if (i > 0) return 1;

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return -1;
    }

    //jdbc修改方法
    public static int update(String id, String name, String pwd, String age) {
        try {
            Connection conn = BaseUtil.getConn();
            PreparedStatement ps = conn.prepareStatement("update t_emp set  name=?,pwd=?,age=? where id=?");

            ps.setString(1, name);
            ps.setString(2, pwd);
            ps.setString(3, age);
            ps.setString(4, id);

            int i = ps.executeUpdate();
            if (i > 0) return 1;


        } catch (SQLException e) {
            e.printStackTrace();
        }
        return -1;
    }

    //jdbc修改方法
    public static int delete(String id) {
        try {
            Connection conn = BaseUtil.getConn();
            PreparedStatement ps = conn.prepareStatement("delete  from t_emp  where id=?");
            ps.setString(1, id);

            int i = ps.executeUpdate();
            if (i > 0) return 1;


        } catch (SQLException e) {
            e.printStackTrace();
        }
        return -1;
    }


}

 

　　

7. 编写增删改查的测试

7.1 添加测试

package com.dhx.test;

import com.dhx.dao.EmpDao;
public class InsertTest {
    //测试添加方法
    public static void main(String[] args) {
        int insert = EmpDao.insert("张一","111","23");
        if(insert==1){
            System.out.println("添加成功");
        }else if(insert==-1){
            System.out.println("添加失败");
        }

    }
}

 

7.2 删除测试

package com.dhx.test;

import com.dhx.dao.EmpDao;

public class DeleteTest {

    //测试删除方法
    public static void main(String[] args) {
        int delete = EmpDao.delete("8");
        if (delete == 1) {
            System.out.println("删除成功");
        } else {
            System.out.println("删除失败");
        }
    }
}

 

7.3  修改测试

package com.dhx.test;

import com.dhx.dao.EmpDao;


public class UpdateTest {
//测试修改方法
    public static void main(String[] args) {
        int update = EmpDao.update("5","dde","1","1");
        if(update==1){
            System.out.println("修改成功");
        }else{
            System.out.println("修改失败");
        }
    }
}

 

7.4 查询测试

package com.dhx.test;

import com.dhx.dao.EmpDao;
import com.dhx.entity.Emp;

import java.util.List;

public class SelectTest {
    //测试查询方法
    public static void main(String[] args) {
        List<Emp> select = EmpDao.select();
        System.out.println(select);
    }
}