jdbc 数据库操作

对这个数据库进行操作

1，在pom文件dependencies里面配置依赖

		<dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.49</version>
		</dependency>

		<dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.22</version>
            <scope>provided</scope>
        </dependency>

2，编写用户实体类

//这里用lombok插件自动生成每个属性的get和set方法，还有带参和无参函数。引入代码在“1”
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.experimental.Accessors;

@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
@Accessors(chain = true) //链式编程
public class User {
    private int id;
    private String userName;
    private String userPass;
}

3，编写用户接口

public interface UserDao {
	//插入
    void insert(User user) throws Exception;
    //修改
    int update(User user) throws Exception;
    //删除
    int delete(int id) throws Exception;
}

4，实现类

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;

public class UserDaoImpl implements UserDao{
	//创建conection
    private final String url = "jdbc:mysql://localhost:3306/aad?useSSL=false&charactorEncoding=utf8";
    private final String user1 = "root";
    private final String password = "123456";
	//插入
    @Override
    public void insert(User user) throws Exception{
        //加载驱动器类
        Class.forName("com.mysql.jdbc.Driver");
        Connection con = DriverManager.getConnection(url,user1,password);
        //创间statement对象
        String sql = "insert into admin(userName,userPass) values(?,?)";
        PreparedStatement pstm = con.prepareStatement(sql);
        //设置参数
        pstm.setString(1, user.getUserName());
        pstm.setString(2,user.getUserPass());
        //执行sql
        int r = pstm.executeUpdate();
        //输出
        System.out.println(r);
        //关闭资源
        pstm.close();
        con.close();
    }
    //修改
    @Override
    public int update(User user) throws Exception {
        Class.forName("com.mysql.jdbc.Driver");
        Connection con = DriverManager.getConnection(url,user1,password);
        String sql = "update admin set userName = ?, userPass = ? where id = ?";
        PreparedStatement pstm = con.prepareStatement(sql);
        pstm.setString(1,user.getUserName());
        pstm.setString(2,user.getUserPass());
        pstm.setInt(3,user.getId());
        int r = pstm.executeUpdate();
        pstm.close();
        con.close();
        return r;
    }
    //删除
    @Override
    public int delete(int id) throws Exception {
        Class.forName("com.mysql.jdbc.Driver");
        Connection con = DriverManager.getConnection(url,user1,password);
        String sql = "delete from admin where id = ?";
        PreparedStatement pstm = con.prepareStatement(sql);
        pstm.setInt(1,id);
        int r = pstm.executeUpdate();
        return r;
    }
}

5，使用junit测试

①插入测试

import org.junit.Test;

public class UserDaoImplTest {
    //插入测试
    @Test
    public void testInsert() throws Exception{
        User user = new User()
                .setUserName("芜湖")
                .setUserPass("123456");
        UserDao userDao = new UserDaoImpl();
        userDao.insert(user);
    }
}

可以看到成功插入

②修改测试

//修改测试
    @Test
    public void testUpdate() throws Exception {
        User user = new User()
                .setId(1)
                .setUserName("小明")
                .setUserPass("55555");
        userDao.update(user);
    }

可以看到id为１的用户修改成功！

③删除测试

 //删除测试
    @Test
    public void testDelete() throws Exception {
        int id = 2;
        userDao.delete(id);
    }

id为２的数据被删除

ps:第一次写所有有很多重复代码，后面会修改封装。

6，对重复代码进行改善封装，编写一个工具类

创建一个jdbcutil类

public final class jdbcUtil01 {
    private static final String url = "jdbc:mysql://localhost:3306/aad?useSSL=false&charactorEncoding=utf8";
    private static final String user1 = "root";
    private static final String password = "123456";
    private jdbcUtil01() {
    }
    //加载驱动器类，只执行一次就可以了，无需返回
    static {
        try {
            Class.forName("com.mysql.jdbc.Driver");
        }catch (Exception e) {
            //e.printStackTrace();
            throw new RuntimeException("加载驱动器类出错，错误信息为" + e.getMessage());
        }
    }

    public static int  update(String sql, Object... params) {
        Connection con =null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        try {
            //加载驱动器类
            Class.forName("com.mysql.jdbc.Driver");
            //创建连接对象
            con = DriverManager.getConnection(url,user1,password);
            //创建执行sql语句对象
            pstm = con.prepareStatement(sql);
            //设置参数
            for(int i = 0; i < params.length; i++) {
                pstm.setObject(i + 1,params[i]);
            }
            //执行sql
            return pstm.executeUpdate();
        }catch (Exception e) {
            e.printStackTrace();
            return 0;
        }finally {
            distory(con,pstm,rs);
        }
    }

    //通用的释放资源方法。
    public static void distory(Connection con, PreparedStatement pstm, ResultSet rs) {
        //释放资源
        if(rs != null) {
            try{
                rs.close();
            }catch (Exception e) {
                e.printStackTrace();
            }
        }
        if(pstm != null) {
            try{
                pstm.close();
            }catch (Exception e) {
                e.printStackTrace();
            }
        }
        if(con != null) {
            try{
                con.close();
            }catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
}