连接mysql数据库的操作 有增删改查
用的包有
commons-lang3-3.5
mysql-connector-java-5.1.40-bin
但是实际上也就是
数据查询和数据处理两种
所以对数据库的操作DAO只有两种方法
package com.javaweb.dao;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import com.javaweb.utils.StringUtlis;
import com.javaweb.utils.DBUtlis;
import com.javaweb.utils.DateUtlis;
/*
* 数据库操作类
* */
public class DBDao<T> {
public <T> List<T> findAll(String sql,Class<T> clazz,List<Object> params)throws Exception{
List<T> list = new ArrayList<T>();
Connection conn=DBUtlis.getConnection();
PreparedStatement pstmt = null;
ResultSet rs = null;
Field field=null;
int index=1;
try {
pstmt = conn.prepareStatement(sql);
if(params != null && !params.isEmpty()){
for(int i=0; i<params.size(); i++){
pstmt.setObject(index++, params.get(i));
}
}
rs = pstmt.executeQuery();
ResultSetMetaData metaData = rs.getMetaData();
int colsCount= metaData.getColumnCount();
while(rs.next()){
T obj=clazz.newInstance();
for(int i = 0; i<colsCount; i++){
String colsName = metaData.getColumnName(i+1);
Object colsValue = rs.getObject(colsName);
if(colsValue == null){
colsValue = "";
}
try{
field=clazz.getDeclaredField(StringUtlis.camelName(colsName));
}catch(NoSuchFieldException e){
Class clazz1=clazz.getSuperclass();
try{
field=clazz1.getDeclaredField(StringUtlis.camelName(colsName));
}catch(NoSuchFieldException n){
}
}
if(field!=null){
field.setAccessible(true);
if(colsValue instanceof java.sql.Date){
field.set(obj, DateUtlis.getStrDate((Date)colsValue));
}else{
field.set(obj, colsValue);
}
}
}
list.add(obj);
}
} catch (SQLException e) {
e.printStackTrace();
} catch (InstantiationException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (SecurityException e) {
e.printStackTrace();
}finally{
DBUtlis.close(conn, pstmt, rs);
}
return list;
}
public int execute(String sql, List<Object> params) {
// TODO Auto-generated method stub
int result = 0;
Connection conn = null;
PreparedStatement ps = null;
int index=1;
try {
conn = DBUtlis.getConnection();
ps = conn.prepareStatement(sql);
if(params != null && !params.isEmpty()){
for(int i=0; i<params.size(); i++){
ps.setObject(index++, params.get(i));
}
}
result = ps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
result = -1;
} catch (Exception e) {
e.printStackTrace();
} finally {
DBUtlis.close(conn, ps, null);
}
return result;
}
}
补：后来我才发现这玩意应该有高手写到jar包里面的.....
其实里面的意思也不是数据查询和数据操作
而是得到一个结果和多个结果的区别
对数据的Dao的处理
package com.javaweb.dao;
import java.util.ArrayList;
import java.util.List;
import org.apache.commons.lang3.StringUtils;
import com.javaweb.dao.DBDao;
import com.javaweb.bean.User;
public class UserDao {
private DBDao dao=new DBDao();
public List<User> findAll(){
List<User> list=new ArrayList<User>();
StringBuffer sql=new StringBuffer();
sql.append(" select id,username,password ");
sql.append(" from user ");
try {
list=dao.findAll(sql.toString(), User.class, null);
} catch (Exception e) {
e.printStackTrace();
}
return list;
}
public int add(User User) {
// TODO Auto-generated method stub
if(User==null){
return 0;
}
StringBuffer sql=new StringBuffer();
sql.append(" insert into user(id,username,password) ");
sql.append(" values(?,?,?) ");
List<Object> params =new ArrayList<Object>();
params.add(User.getId());
params.add(User.getUsername());
params.add(User.getPassword());
return dao.execute(sql.toString(), params);
}
public int delete(User user) {
// TODO Auto-generated method stub
if(user==null){
return 0;
}
StringBuffer sql=new StringBuffer();
sql.append(" delete from user where id=?");
List<Object> params =new ArrayList<Object>();
params.add(user.getId());
return dao.execute(sql.toString(), params);
}
public int update(User user) {
// TODO Auto-generated method stub
if(user==null){
return 0;
}
List<Object> params =new ArrayList<Object>();
StringBuffer sql=new StringBuffer();
sql.append(" update user set ");
if(StringUtils.isNoneBlank(user.getUsername())){
sql.append(" username= ?, ");
params.add(user.getUsername());
}
if(StringUtils.isNoneBlank(user.getPassword())){
sql.append(" password= ?,");
params.add(user.getPassword());
}
String strSql=sql.toString().substring(0, sql.toString().length()-1)+" where id=? ";
params.add(user.getId());
return dao.execute(strSql, params);
}
}
User类
package com.javaweb.bean;
public class User {
private String id;
private String username;
private String password;
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
}
servlet
package com.javaweb.action;
import java.io.IOException;
import java.util.List;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.commons.lang3.StringUtils;
import com.javaweb.bean.User;
import com.javaweb.dao.UserDao;
public class ServletSql extends HttpServlet{
/**
* 用于版本控制
*/
private static final long serialVersionUID = -2357925750878300415L;
private UserDao dao=new UserDao();
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
// TODO Auto-generated method stub
doPost(req, resp);
}
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
// TODO Auto-generated method stub
//纯碎是用来判断有没有错误
req.setCharacterEncoding("UTF-8");
String op=req.getParameter("op");
if(StringUtils.isNotBlank(op)){
if("queryAll".equalsIgnoreCase(op)){
queryAll(req, resp);
}else if("add".equalsIgnoreCase(op)){
add(req, resp);
}else if("delete".equalsIgnoreCase(op)){
delete(req, resp);
}else if("update".equalsIgnoreCase(op)){
update(req, resp);
}else{
}
}
}
private void update(HttpServletRequest req, HttpServletResponse resp) {
// TODO Auto-generated method stub
User user;
try {
user = init(req,resp);
user.setId(req.getParameter("id"));
int rows=dao.update(user);
if(rows>0){
queryAll(req, resp);
}
} catch (ServletException|IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
private void delete(HttpServletRequest req, HttpServletResponse resp) {
// TODO Auto-generated method stub
User user=new User();
String id=req.getParameter("id");
user.setId(id);
int rows=dao.delete(user);
if(rows>0){
try {
queryAll(req, resp);
} catch (ServletException|IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
public User init(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
User user=new User();
String id=req.getParameter("id");
// user.setId(Integer.parseInt(id));
user.setId(id);
String name=req.getParameter("name");
user.setUsername(name);
String password=req.getParameter("password");
// user.setPassword(Integer.parseInt(password));
user.setPassword(password);
return user;
}
public void add(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
User User=init(req,resp);
int rows=dao.add(User);
if(rows>0){
queryAll(req, resp);
}else{
resp.sendRedirect("index.jsp");
}
}
public void queryAll(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
List<User> list=dao.findAll();
req.setAttribute("list", list);
req.getRequestDispatcher("/queryAll.jsp").forward(req, resp);
// resp.sendRedirect("queryAll.jsp");
}
}
有几个方法后接throws ServletException, IOException应该会更加的整洁
.properties文件
#\u6570\u636e\u8fde\u63a5\u914d\u7f6e db.driver=com.mysql.jdbc.Driver db.url=jdbc:mysql://localhost:/javaweb db.userName=root db.password=
DateUtils
package com.javaweb.utils;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
public class DateUtlis {
/**
* 获取当前日期
* @return
*/
public static String getCurrentTime() {
String returnStr = null;
SimpleDateFormat f = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
Date date = new Date();
returnStr = f.format(date);
return returnStr;
}
public static Date getDate(String strDate){
Date date=null;
SimpleDateFormat f = new SimpleDateFormat("yyyy-MM-dd");
try {
date = f.parse(strDate);
} catch (ParseException e) {
e.printStackTrace();
}
return date;
}
public static String getStrDate(Date date){
String strDate=null;
SimpleDateFormat f = new SimpleDateFormat("yyyy-MM-dd");
strDate = f.format(date);
return strDate;
}
}
DButils
package com.javaweb.utils;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
/**
* 数据库连接类
* @author SUMMER
*
*/
public class DBUtlis {
private static String driver;
private static String url;
private static String userName;
private static String password;
static{
driver=PropertiesUtil.getProperty("db.driver");
url=PropertiesUtil.getProperty("db.url");
userName=PropertiesUtil.getProperty("db.userName");
password=PropertiesUtil.getProperty("db.password");
}
/**
* 创建数据库连接
* @return
*/
public static Connection getConnection() {
Connection conn = null;
try {
Class.forName(driver);
conn = java.sql.DriverManager.getConnection(url,userName, password);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
return conn;
}
public static void close(Connection conn, PreparedStatement psm,ResultSet rs) {
close(rs);
close(psm);
close(conn);
}
public static void close(Connection conn, Statement st,ResultSet rs) {
close(rs);
close(st);
close(conn);
}
public static void close(Connection conn, PreparedStatement psm) {
close(psm);
close(conn);
}
public static void close(Connection conn) {
if (null != conn) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void close(ResultSet rs) {
if (null != rs) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void close(PreparedStatement psm) {
if (null != psm) {
try {
psm.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void close(CallableStatement proc) {
if (null != proc) {
try {
proc.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/**
* @param st
*/
public static void close(Statement st) {
if (null != st) {
try {
st.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/**
* @param conn
*/
public static void rollback(Connection conn) {
if(conn != null) {
try {
conn.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
}
}
/**
* @param conn
* @param auto
*/
public static void setAutoCommit(Connection conn, boolean auto) {
if(conn != null) {
try {
conn.setAutoCommit(auto);
} catch (SQLException e1) {
e1.printStackTrace();
}
}
}
public static void main(String[] args) {
DBUtlis.getConnection();
}
}
propertiesUtils
package com.javaweb.utils;
import java.io.IOException;
import java.io.InputStream;
import java.util.Properties;
/**
* 读取properties文件
* @author SUMMER
*
*/
public class PropertiesUtil {
private static Properties prop;
/**
* 加载.properties文件,使用文件流
*/
private static void init() {
prop = new Properties();
InputStream is=PropertiesUtil.class.getResourceAsStream("/config/db.properties");
try {
prop.load(is);
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* 获取value
* @param key
* @return
*/
public static String getProperty(String key) {
if (prop == null ) {
init();
}
return prop.get(key).toString();
}
public static void main(String[] args) {
System.out.println(getProperty("db.driver"));
}
}
StringUtils
package com.javaweb.utils;
import java.util.UUID;
/**
* 字符串处理工具类
*
*/
public class StringUtlis {
/**
* 获取唯一ID
* @return
*/
public static String getUUID() {
UUID uuid = UUID.randomUUID();
String str = uuid.toString();
return str.replace("-", "");
}
/**
* 将驼峰式命名的字符串转换为下划线大写方式。如果转换前的驼峰式命名的字符串为空，则返回空字符串。</br>
* 例如：HelloWorld->HELLO_WORLD
* @param name 转换前的驼峰式命名的字符串
* @return 转换后下划线大写方式命名的字符串
*/
public static String underscoreName(String name) {
StringBuilder result = new StringBuilder();
if (name != null && name.length() > 0) {
// 将第一个字符处理成大写
result.append(name.substring(0, 1).toUpperCase());
// 循环处理其余字符
for (int i = 1; i < name.length(); i++) {
String s = name.substring(i, i + 1);
// 在大写字母前添加下划线
if (s.equals(s.toUpperCase()) && !Character.isDigit(s.charAt(0))) {
result.append("_");
}
// 其他字符直接转成大写
result.append(s.toUpperCase());
}
}
return result.toString();
}
/**
* 将下划线大写方式命名的字符串转换为驼峰式。如果转换前的下划线大写方式命名的字符串为空，则返回空字符串。</br>
* 例如：HELLO_WORLD->HelloWorld
* @param name 转换前的下划线大写方式命名的字符串
* @return 转换后的驼峰式命名的字符串
*/
public static String camelName(String name) {
StringBuilder result = new StringBuilder();
// 快速检查
if (name == null || name.isEmpty()) {
// 没必要转换
return "";
} else if (!name.contains("_")) {
// 不含下划线，仅将首字母小写
return name.substring(0, 1).toLowerCase() + name.substring(1);
}
// 用下划线将原始字符串分割
String camels[] = name.split("_");
for (String camel : camels) {
// 跳过原始字符串中开头、结尾的下换线或双重下划线
if (camel.isEmpty()) {
continue;
}
// 处理真正的驼峰片段
if (result.length() == 0) {
// 第一个驼峰片段，全部字母都小写
result.append(camel.toLowerCase());
} else {
// 其他的驼峰片段，首字母大写
result.append(camel.substring(0, 1).toUpperCase());
result.append(camel.substring(1).toLowerCase());
}
}
return result.toString();
}
public static void main(String[] args) {
System.out.println(getUUID());
}
}
utils就是工具类了
拿来就用，大多数情况下都有很多高手写jar包含他们
但是我没用那些jar直接上网找的
要求分门别类写，是个好习惯
index.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%
String path = request.getContextPath();// 获得当前的项目根目录路径
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path;
//完整路径
%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>这是首页</title>
</head>
<body>
<table border=0 cellpadding=0 cellspacing=0 style="margin:auto;border-collapse:separate; border-spacing:10px;">
<tr>
<td>
<a href="<%=basePath%>/servletSql?op=queryAll">查询全部</a>
</td>
</tr>
<tr>
<td>
<a href="<%=basePath%>/add.jsp">添加</a>
</td>
</tr>
<tr>
<td>
<a href="<%=basePath%>/delete.jsp">删除</a>
</td>
</tr>
<tr>
<td>
<a href="<%=basePath%>/update.jsp">修改</a>
</td>
</tr>
</table>
</body>
</html>
add
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%
String path = request.getContextPath();// 获得当前的项目根目录路径
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path;
//完整路径
%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
</head>
<body>
<form action="<%=basePath%>/servletSql?op=add" method="post">
<table align="center" border="1">
<tr>
<th colspan="3">
<label>添加信息</label>
</th>
</tr>
<tr>
<td>Id</td>
<td>
<input type="text" name="id" >
</td>
</tr>
<tr>
<td>用户名</td>
<td>
<input type="text" name="name" >
</td>
</tr>
<tr>
<td>密码</td>
<td>
<input type="text" name="password" >
</td>
</tr>
<tr>
<td>
</td>
<td>
<input type="submit" value="保存">
<input type="reset" value="清空">
</td>
</tr>
</table>
</form>
</body>
</html>
delete
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%
String path = request.getContextPath();// 获得当前的项目根目录路径
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path;
//完整路径
%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
</head>
<body>
<form action="<%=basePath%>/servletSql?op=delete" method="post">
<table align="center" border="1">
<tr>
<th colspan="3">
<label>删除信息</label>
</th>
</tr>
<tr>
<td>Id</td>
<td>
<input type="text" name="id" >
</td>
</tr>
<tr>
<td>
</td>
<td>
<input type="submit" value="保存">
<input type="reset" value="清空">
</td>
</tr>
</table>
</form>
</body>
</html>
queryAll
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%
String path = request.getContextPath();// 获得当前的项目根目录路径
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path;
//完整路径
%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
</head>
<body>
<form action="<%=basePath%>/servletSql?op=delete" method="post">
<table align="center" border="1">
<tr>
<th colspan="3">
<label>删除信息</label>
</th>
</tr>
<tr>
<td>Id</td>
<td>
<input type="text" name="id" >
</td>
</tr>
<tr>
<td>
</td>
<td>
<input type="submit" value="保存">
<input type="reset" value="清空">
</td>
</tr>
</table>
</form>
</body>
</html>
update
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%
String path = request.getContextPath();// 获得当前的项目根目录路径
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path;
//完整路径
%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
</head>
<body>
<form action="<%=basePath%>/servletSql?op=update" method="post">
<table align="center" border="1">
<tr>
<th colspan="3">
<label>修改信息</label>
</th>
</tr>
<tr>
<td>Id</td>
<td>
<input type="text" name="id" >
</td>
</tr>
<tr>
<td>用户名</td>
<td>
<input type="text" name="name" >
</td>
</tr>
<tr>
<td>密码</td>
<td>
<input type="text" name="password" >
</td>
</tr>
<tr>
<td>
</td>
<td>
<input type="submit" value="保存">
<input type="reset" value="清空">
</td>
</tr>
</table>
</form>
</body>
</html>