JDBC

说明对JDBC进行简单的封装，方便使用完整代码：https://pan.bigdataboy.cn/#/s/nMC0数据库数据创表DROPTABLEIFEXISTSuser;CREATETABLEuser(idBIGINT(20)NOTNULLauto_incrementCOMMENT'主键ID',nameVARCHAR(30)NULLDEFAULTNULLCOMMENT'姓名',ageINT(11)NULLDEFAULTNULLCOMMENT'年龄',emailVARCHAR(50)NULLDEFAULTNULLCOMMENT'邮箱',PRIMARYKEY(id));加入数据INSERTINTOuser(id,name,age,email)VALUES(1,'Jone',18,'test1@bigdataboy.com'),(2,'Jack',20,'test2@bigdataboy.com'),(3,'Tom',28,'test3@bigdataboy.com'),(4,'Sandy',21,'test4@bigdataboy.com'),(5,'Billie',24,'test5@bigdataboy.com');构建目录工具类连接工具类，封装连接对象，方便获取连接packagecn.bigdataboy.util;importjava.sql.PreparedStatement;importjava.sql.Connection;importjava.sql.DriverManager;importjava.sql.ResultSet;importjava.sql.SQLException;publicclassDBConnectionUtil{//创建连接参数privatestaticStringdriver="com.mysql.jdbc.Driver";privatestaticStringurl="jdbc:mysql://localhost:3306/jdbctest?characterEncoding=utf8";privatestaticStringusername="";privatestaticStringpassword="";//获取连接对象publicstaticConnectiongetConnection(){Connectionconn=null;//获取连接try{//加载驱动Class.forName(driver);conn=DriverManager.getConnection(url,username,password);}catch(SQLExceptionthrowables){throwables.printStackTrace();}catch(ClassNotFoundExceptione){e.printStackTrace();}returnconn;}//关闭全部publicstaticvoidcloseAll(ResultSetrs,PreparedStatementps,Connectionconn){if(rs!=null){try{rs.close();}catch(SQLExceptionthrowables){throwables.printStackTrace();}};if(ps!=null){try{ps.close();}catch(SQLExceptionthrowables){throwables.printStackTrace();}};if(conn!=null){try{conn.close();}catch(SQLExceptionthrowables){throwables.printStackTrace();}}}}测试连接工具类publicstaticvoidmain(String[]args){Connectionconnection=getConnection();System.out.println(connection);}用户实体类用户实体类的字段需要与数据表字段对应packagecn.bigdataboy.entity;publicclassUser{privateintid;privateStringname;privateintage;privateStringemail;publicintgetId(){returnid;}publicvoidsetId(intid){this.id=id;}publicStringgetName(){returnname;}publicvoidsetName(Stringname){this.name=name;}publicintgetAge(){returnage;}publicvoidsetAge(intage){this.age=age;}publicStringgetEmail(){returnemail;}publicvoidsetEmail(Stringemail){this.email=email;}}定义dao层接口dao层主要功能是提供对数据库数据的操作类packagecn.bigdataboy.dao;importcn.bigdataboy.entity.User;publicinterfaceUserDao{//通过id查询用户UsergetUserById(intid);}实现UserDao接口packagecn.bigdataboy.dao.impl;importcn.bigdataboy.dao.UserDao;importcn.bigdataboy.entity.User;importcn.bigdataboy.util.DBConnectionUtil;importjava.sql.Connection;importjava.sql.PreparedStatement;importjava.sql.ResultSet;importjava.sql.SQLException;publicclassUserDaoImplimplementsUserDao{//通过id查询用户publicUsergetUserById(intid){Useruser=null;Connectionconn=null;PreparedStatementps=null;ResultSetrs=null;try{//通过连接工具类获取连接对象conn=DBConnectionUtil.getConnection();Stringsql="select*fromuserwhereid=?";ps=conn.prepareStatement(sql);ps.setInt(1,id);//设置第一个问号的值rs=ps.executeQuery();//执行SQL语句//判断是否返回到值，多个值需要循环使用next()获取if(rs.next()){//设置user值user=newUser();user.setId(rs.getInt("id"));user.setName(rs.getString("name"));user.setAge(rs.getInt("age"));user.setEmail(rs.getString("email"));}}catch(SQLExceptionthrowables){throwables.printStackTrace();}finally{//关闭全部DBConnectionUtil.closeAll(rs,ps,conn);}returnuser;}}测试实现的getUserById()方法packagecn.bigdataboy.dao;importcn.bigdataboy.dao.impl.UserDaoImpl;importcn.bigdataboy.entity.User;importorg.junit.Test;publicclassTestUserDaoImpl{@TestpublicvoidTestGetUserById(){intid=1;UserDaoImpluserDao=newUserDaoImpl();Useruser=userDao.getUserById(id);System.out.println(user.getId());System.out.println(user.getName());System.out.println(user.getAge());System.out.println(user.getEmail());System.out.println(user.getClass());}}其他的修改、添加、删除数据都是一样的，完整代码里有