本文主要是介绍java之实现增删改查的下案例、获取元数据、DBUtils,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!
***数据库操作接口
package dao;import java.util.List;import entity.User;/*** @author HaokeMaster*操作数据库接口*/
public interface UserDAO {public List<User> findAll()throws Exception;//查询所用用户的信息public void delete(int id)throws Exception;//删除用户信息public void save(User e)throws Exception;//增加保存用户信息public void update(User e)throws Exception;//更新一个用户的信息public User findById(int id)throws Exception;//一条记录
public void getData(String tablename)throws Exception;//获取数据库的源数据
public void getPage(int pageSize,int page)throws Exception;//分页查询,page是第几页,pageSize是每页的记录数量
}
***数据库操作实现:
package dao.impls;import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;import com.mysql.cj.jdbc.DatabaseMetaData;
import com.mysql.cj.jdbc.result.ResultSetMetaData;
import com.mysql.cj.protocol.x.ResultMessageListener;import dao.UserDAO;
import entity.User;
import util.DBUtil;public class UserDAOone implements UserDAO {// 添加@Overridepublic void save(User e) throws Exception {Connection conn = DBUtil.getConnection();//使用Statement,sql语句在执行的时候传递;使用PreparedStatement,sql语句在生成语句对象的时候传递PreparedStatement prep = conn.prepareStatement("insert into " + "PEOPLE(id,name,age,phoneNum) " + "values(null,?,?,?)");prep.setString(1, e.getName());//1,2,3表示的是第一第二第三个参数prep.setInt(2, e.getAge());prep.setString(3, e.getPhoneNum());prep.executeUpdate();DBUtil.close(conn);}// 根据id删除@Overridepublic void delete(int id) throws Exception {Connection conn = DBUtil.getConnection();PreparedStatement prep = conn.prepareStatement("delete from PEOPLE where id=?");//预编译语句对象prep.setLong(1, id);prep.executeUpdate();DBUtil.close(conn);}// 更新@Overridepublic void update(User e) throws Exception {Connection conn = DBUtil.getConnection();PreparedStatement prep = conn.prepareStatement("update PEOPLE " + "set name=?,age=?,phoneNum=? " + "where id=?");prep.setString(1, e.getName());prep.setInt(2, e.getAge());prep.setString(3, e.getPhoneNum());prep.setInt(4, e.getId());prep.executeUpdate();DBUtil.close(conn);}//根据id查询一条记录@Overridepublic User findById(int id) throws Exception {Connection conn = DBUtil.getConnection();PreparedStatement prep = conn.prepareStatement("select * " + "from PEOPLE where id=?");prep.setInt(1, id);ResultSet rs = prep.executeQuery();User e = null;if (rs.next()) {e = new User();e.setName(rs.getString("name"));e.setId(rs.getInt("id"));e.setAge(rs.getInt("age"));e.setPhoneNum(rs.getString("phoneNum"));}DBUtil.close(conn);return e;}// 查询所有@Overridepublic List<User> findAll() throws Exception {Connection conn = DBUtil.getConnection();Statement stmt = conn.createStatement();ResultSet rs = stmt.executeQuery("select * from PEOPLE");List<User> users = new ArrayList<User>();while (rs.next()) {User e = new User();e.setName(rs.getString("name"));e.setId(rs.getInt("id"));e.setAge(rs.getInt("age"));e.setPhoneNum(rs.getString("phoneNum"));users.add(e);}DBUtil.close(conn);return users;}//获取数据库的元数据public void getData(String tablename)throws Exception{String sql="select *from " + tablename;Connection conn = DBUtil.getConnection();Statement stmt = conn.createStatement();ResultSet rs = stmt.executeQuery(sql);try {//获取数据库的元数据DatabaseMetaData dmd=(DatabaseMetaData) conn.getMetaData();//数据库名String dbname=dmd.getDatabaseProductName();//数据库的版本号int version=dmd.getDatabaseMajorVersion();//数据库的连接地址String url=dmd.getURL();//数据库用户名dmd.getUserName();//获取结果集元数据ResultSetMetaData rsmd=(ResultSetMetaData) rs.getMetaData();//获取列数int colCount=rsmd.getColumnCount();//获取第i列的字段名,jdbc中字段的序号从1开始;String colname= rsmd.getColumnName(1);} catch (Exception e) {// TODO: handle exception}}//分页查询,page是第几页,pageSize是每页的记录数量public void getPage(int pageSize,int page)throws Exception{int begin =(page-1)*pageSize+1;// 记录的起点String sql="select *from PEOPLE";Connection conn = DBUtil.getConnection();Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);//第一个参数是表示可滚动的结果集,可以跳步;第二个参数设置并发性其他用户只读ResultSet rs = stmt.executeQuery(sql);//指针定位到绝对位置,第三条记录rs.absolute(3);//定位到相对位置,相对于上一个的位置基础上加5.到第8条记录rs.relative(5);//下一条,第9条记录rs.next();//前一条,第8条记录rs.previous();}}
****实体类:
package entity;/*** @author HaokeMaster 实体类*/
public class User {private int id;private String name;private int age;private String phoneNum;public String getPhoneNum() {return phoneNum;}public void setPhoneNum(String phoneNum) {this.phoneNum = phoneNum;}public String getName() {return name;}public void setName(String name) {this.name = name;}public int getAge() {return age;}public void setAge(int age) {this.age = age;}public void setId(int id) {this.id = id;}public int getId() {return id;}}
****配置文件工具类:
package util;import java.io.InputStream;
import java.util.Properties;/*** @author HaokeMaster*配置文件读取的工具*使用:ConfigUtil.getPropertiesParam("mysqlUse.properties");*String url= ConfigUtil.getValue("url");*/
public class ConfigUtil {
private static Properties propes=new Properties();
//static代码块: static{}
//static代码块也叫静态代码块,是在类中独立于类成员的static语句块,可以有多个,位置可以随便放,
//它不在任何的方法体内,JVM加载类时会执行这些静态的代码块,如果static代码块有多个,
//JVM将按照它们在类中出现的先后顺序依次执行它们,每个代码块只会被执行一次//获取属性文件中的内容public static void getPropertiesParam(String filename) {//***方法1。在classpath的目录下(即在src的目录下)用getclassload方法获取ClassLoader loader=ConfigUtil.class.getClassLoader();//获取类加载器try {InputStream fis = loader.getResourceAsStream(filename); //加载输入流指定的文件propes.load(fis);}catch (Exception e) {}}//获取配置文件中的键值,key是配置文件中的键public static String getValue(String key) {return propes.getProperty(key);}}
*****数据库连接和关闭工具类:
package util;import java.io.File;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;/*** @author HaokeMaster 数据连接和关闭的工具类* commmand +shift+o 自动导包*/
public class DBUtil {private static String url;private static String dbUser;private static String dbPassword;//获取属性文件中的内容public static void getPropertiesParam(String filename) {
// //***方法1。在classpath的目录下(即在src的目录下)用getclassload方法获取
// Properties propes=new Properties();
// try {
// InputStream fis = DBUtil.class.getClassLoader().getResourceAsStream(filename);
// //加载输入流指定的文件
// propes.load(fis);
// //获取文件中对应的键的值
// url=propes.getProperty("url");
// dbUser=propes.getProperty("dbUser");
// dbPassword=propes.getProperty("dbPassword");
//
// System.out.println(url);
// }catch (Exception e) {
//
// }//*****方法2.不在classpath的目录下(即不在src的目录下),必须使用完整路径Properties propes=new Properties();File file=new File(filename);try {FileInputStream fis=new FileInputStream(file);//加载输入流指定的文件propes.load(fis);//获取文件中对应的键的值url=propes.getProperty("url");dbUser=propes.getProperty("dbUser");dbPassword=propes.getProperty("dbPassword");System.out.println(url);}catch (Exception e) {}}// 建立数据库连接public static Connection getConnection() throws Exception {DBUtil.getPropertiesParam("/Users/HaokeMaster/Desktop/javatest01/First/src/mysqlUse.properties");//对应上面的方法2;不在classpath的目录下(即在src的目录下),这个必须是完整路径
// DBUtil.getPropertiesParam("mysqlUse.properties");//对应上面的方法1;在classpath的目录下(即在src的目录下),直接写文件名加后缀Connection conn = null;try {Class.forName("com.mysql.cj.jdbc.Driver");// 加载MYSQL JDBC驱动程序
// conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/firstDB", "root",
// "1234567890");// 连接URL为 jdbc:mysql//服务器地址:端口号/数据库名 ,后面的2个参数分别是登陆用户名和密码conn = DriverManager.getConnection(url, dbUser,dbPassword);} catch (Exception e) {e.printStackTrace();throw e;}return conn;}// 关闭数据库连接public static void close(Connection conn){if(conn!=null){try {conn.close();} catch (SQLException e) { e.printStackTrace();} }}// 用于测试 DBUtilpublic static void main(String[] args) throws Exception {System.out.println(getConnection());}
}
******工厂类:
package util;import dao.impls.UserDAOone;/*** @author HaokeMaster*工厂方法*/
public class DAOFactory {public static Object getInstance(String type){ Object obj = null;if(type.equals("UserDAOone")){obj = new UserDAOone();}return obj; }
}
****添加用户Servlet:
package web;import java.io.IOException;import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;import dao.impls.UserDAOone;
import entity.User;/*** Servlet implementation class AddUserServlet* 添加用户*/
@WebServlet("/AddUserServlet")
public class AddUserServlet extends HttpServlet {public void service(HttpServletRequest request, HttpServletResponse response)throws ServletException,IOException{//这行代码要放在 getParameter()执行前。request.setCharacterEncoding("utf-8");String name = request.getParameter("name"); int age = Integer.parseInt(request.getParameter("age"));String phoneN = request.getParameter("phoneNum");System.out.println("姓名"+name+","+"年龄"+name+"手机号码"+phoneN);try {UserDAOone dao =new UserDAOone(); User e = new User();e.setName(name);e.setAge(age);e.setPhoneNum(phoneN);// 添加用户dao.save(e);System.out.println(e);//重定向到list,是get请求response.sendRedirect("list");} catch (Exception e) {//step1 先记录日志e.printStackTrace();//step2 抛出throw new ServletException(e);}}
}
*****删除用户Servlet:
package web;import java.io.IOException;import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;import dao.impls.UserDAOone;/*** Servlet implementation class DeleteUserServlet* 删除*/
@WebServlet("/DeleteUserServlet")
public class DeleteUserServlet extends HttpServlet {public void service( HttpServletRequest request,HttpServletResponse response) throws ServletException,IOException{int id =Integer.parseInt( request.getParameter("id")); try {UserDAOone dao =new UserDAOone();// 删除指定id的用户dao.delete(id);//重定向到用户列表response.sendRedirect("list"); }catch(Exception e){e.printStackTrace();throw new ServletException(e);} }}
****修改用户Servlet:
package web;import java.io.IOException;import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;import dao.impls.UserDAOone;
import entity.User;/*** Servlet implementation class ModifyUserServlet* 修改用户信息*/
@WebServlet("/ModifyUserServlet")
public class ModifyUserServlet extends HttpServlet {public void service(HttpServletRequest request,HttpServletResponse response) throws ServletException,IOException{request.setCharacterEncoding("utf-8");//设置浏览器或app发过来的参数时的编码方式int id =Integer.parseInt( request.getParameter("id"));//客户端发过来的参数都是字符串String name = request.getParameter("name");int age = Integer.parseInt( request.getParameter("age"));String phoneN=request.getParameter("phoneNum");try {UserDAOone dao =new UserDAOone();User e = new User(); e.setId(id);e.setName(name); e.setAge(age); e.setPhoneNum(phoneN);//更新用户信息dao.update(e);// 重定向到用户列表response.sendRedirect("list");}catch(Exception e){e.printStackTrace();throw new ServletException(e); }}}
****用户列表Servlet:
package web;import java.io.IOException;
import java.io.PrintWriter;
import java.util.List;import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;import dao.impls.UserDAOone;
import entity.User;
import util.DAOFactory;/*** Servlet implementation class ListUserServlet* 查看用户列表*/
@WebServlet("/ListUserServlet")
public class ListUserServlet extends HttpServlet {public void service(HttpServletRequest request,HttpServletResponse response)throws ServletException,IOException{ //使用 dao 访问数据库try {UserDAOone dao = (UserDAOone) DAOFactory .getInstance("UserDAOone");List<User> users =dao.findAll(); //使用查询得到的结果,生成一个表格response.setContentType("text/ html;charset=utf-8");PrintWriter out = response.getWriter();out.println("<table border='1' width='60%' " + "cellpadding='0' cellspacing='0'>");out.println("<tr><td>id</td>" +"<td>姓名</td>" +"<td>薪水</td>" + "<td>年龄</td>" +"<td>操作</td></tr>"); for(int i=0;i<users.size();i++){User e = users.get(i);out.println( "<tr><td>"+ e.getId() + "</td><td> "+ e.getName() + "</td><td>" + e.getAge() + "</td><td> " + e.getPhoneNum() + "</td><td>" + "<a href='delete?id="+ e.getId() + "'>删除</a>" + " <a href='load?id="+ e.getId() +"'>修改" + "</a></td></tr>");}out.println( "</table>");out.println("<a href='index.jsp'>" + "增加新雇员</a>"); out.close();} catch (Exception e) {e.printStackTrace();throw new ServletException(e); }}
}
***修改用户信息Servlet:
package web;import java.io.IOException;
import java.io.PrintWriter;import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;import dao.impls.UserDAOone;
import entity.User;/*** Servlet implementation class LoadEmptyUserServlet* 修改页用户信息的页面,从LisUserServlet.java 中的修改按钮来。*/
@WebServlet("/LoadEmptyUserServlet")
public class LoadEmptyUserServlet extends HttpServlet {public void service(HttpServletRequest request,HttpServletResponse response) throws ServletException,IOException{int id =Integer.parseInt( request.getParameter("id"));try {UserDAOone dao =new UserDAOone();User e = dao.findById(id);response.setContentType("text/html;charset=utf-8");PrintWriter out = response.getWriter();out.println("<form action='modify?id="+ id + "' method='post'>"); out.println("id:" + id + "<br/>");out.println("姓名:<input name='name' value='" + e.getName() + "'/><br/>");out.println("年龄:<input name='age' value='"+ e.getAge() + "'/><br/>"); out.println("手机号:<input name='phoneNum' value='"+ e.getPhoneNum() + "'/><br/>"); out.println("<input type='submit' " +"value='确认'/>"); out.println( "</form>");out.close();}catch(Exception e){ e.printStackTrace();throw new ServletException(e); }}}
******配置文件mysqlUse.properties:
url=jdbc:mysql://127.0.0.1:3306/firstDB
dbUser=root
dbPassword=1234567890
虚拟主机在tomcat中的server.xml中配置
******元数据:数据库中表,列、库的定义信息。
DataBaseMetaData数据库元数据、
ParameterMetaData参数元数据:主要获取sql语句中?占位符的信息;注意:在获取参数类型时会产生异常,解决:在url后面添加参数generateSimpleParameterMetada=true;;
ReseultSetMetaData结果集元数
//获取数据库的元数据
public void getData(String tablename)throws Exception{
String sql="select *from " + tablename;
Connection conn = DBUtil.getConnection();
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);
try {
//获取数据库的元数据
DatabaseMetaData dmd=(DatabaseMetaData) conn.getMetaData();
//数据库名
String dbname=dmd.getDatabaseProductName();
//数据库的版本号
int version=dmd.getDatabaseMajorVersion();
//数据库的连接地址
String url=dmd.getURL();
//数据库用户名
dmd.getUserName();
//获取结果集元数据
ResultSetMetaData rsmd=(ResultSetMetaData) rs.getMetaData();
//获取列数
int colCount=rsmd.getColumnCount();
//获取第i列的字段名,jdbc中字段的序号从1开始;
String colname= rsmd.getColumnName(1);
} catch (Exception e) {
// TODO: handle exception
}
}
***DBUtils:jdbc封装工具;是apache的工具,需要导入jar包comons-dbutils.jar;
DBUtils中:
**QueryRunner是框架的核心类,所有数据库的操作丢必须通过queryRunner进行;
query用于执行select语句,update用于执行insert,update,delete;batch批处理;
QueryRunner在创建的时候没有传递Datasource参数,使用的query,update,insert的时候要传递参数,这时是手动事务;
QueryRunner在创建的时候传递Datasource参数,使用的query,update,insert的时候不需要传递参数,这时是自动事务;;
**ResultSetHandler:结果集封装接口,将ResultSet封装为一个java对象;一个可以封装成单个对象,也可封装成list集合。
ResultSetHandler的九个实现类:
*ArrayHandler:将结果集第一条记录封装到Objct数组中,数组中的每一个元素是字段值;
*ArrayListHandler:将结果集每一条记录放到一个数组,然后把所有数组放到list集合中;
*ColumnListHandler:将结果集中指定列封装到list集合;
KedHandler;每一条记录封装到Map中,key是字段的名,value是记录map的封装;
MapHandler:将结果集中第一条记录封装到Map集合中,key是字段名,value是字段值;
MapListHandler;
*BeanHandler:将结果集中的第一条记录直接封装成javaBean;
*BeanListHandler:
*ScalarHandler:单值查询封装。
**Dbutils工具类提供驱动管理,事务管理,释放资源等;
使用案例:https://www.cnblogs.com/wangrongchen/p/9303502.html
这篇关于java之实现增删改查的下案例、获取元数据、DBUtils的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!