20190813JDBC

最新推荐文章于 2022-02-07 16:37:52 发布

丸锃卉

最新推荐文章于 2022-02-07 16:37:52 发布

阅读量134

点赞数 1

本文链接：https://blog.csdn.net/mrwanzh/article/details/99494683

版权

maven settings.xml

<localRepository>F:\Java\Apache\repository</localRepository>

<mirror>  
    <id>alimaven</id>  
    <name>aliyun maven</name>  
    <url>http://maven.aliyun.com/nexus/content/groups/public/</url>
    <mirrorOf>central</mirrorOf>          
</mirror> 
<mirror>
    <id>ui</id>
    <mirrorOf>central</mirrorOf>
    <name>Human Readable Name for this Mirror.</name>
    <url>http://uk.maven.org/maven2/</url>
</mirror>

db.properties：

#mysql connection info
mysql_driver=com.mysql.cj.jdbc.Driver
mysql_url=jdbc:mysql://127.0.0.1:3306/testdemo?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai
mysql_user=root
mysql_password=123456

#oracle connection info
#driver=com.oracle.driver.OracleDriver
#url=jdbc:oracle:thin:@127.0.0.1:1521:orcl
#user=scott
#password=tiger

#pool config###
initSize = 10
maxSize = 50
maxIdle = 30
maxWait = 10000
minIdle = 10
increment = 5
retry = 10

DBConnection：

public class DBConnection {
    private static String DRIVER;
    private static String URL;
    private static String USER;
    private static String PASSWORD;

    static {
        try {
            //读取属性文件
            Properties props = System.getProperties();
            //加载指定属性文件
            props.load(new FileInputStream("src/main/java/db.properties"));
            DRIVER = props.getProperty("mysql_driver");
            URL = props.getProperty("mysql_url");
            USER = props.getProperty("mysql_user");
            PASSWORD = props.getProperty("mysql_password");
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public static Connection getConn(){
        try {
            Class.forName(DRIVER);
            return DriverManager.getConnection(URL, USER, PASSWORD);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    //封装资源回收的方法
    public static void close(ResultSet rs, Statement stat, Connection conn){
        try {
            if (rs != null)rs.close();
            if (stat != null)stat.close();
            if (conn != null)conn.close();
        }catch (SQLException e) {
            e.printStackTrace();
        }
    }

}

pom.xml：

<?xml version="1.0" encoding="UTF-8"?>

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>

  <groupId>com.wan</groupId>
  <artifactId>TestJDBC-demo</artifactId>
  <version>1.0-SNAPSHOT</version>
  <packaging>war</packaging>

  <name>TestJDBC-demo Maven Webapp</name>
  <!-- FIXME change it to the project's website -->
  <url>http://www.example.com</url>

  <properties>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    <maven.compiler.source>1.7</maven.compiler.source>
    <maven.compiler.target>1.7</maven.compiler.target>
  </properties>

  <dependencies>
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>4.11</version>
      <scope>test</scope>
    </dependency>
    <dependency>
      <groupId>org.projectlombok</groupId>
      <artifactId>lombok</artifactId>
      <version>1.18.8</version>
      <scope>provided</scope>
    </dependency>
    <!--mysql驱动包-->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.13</version>
    </dependency>
    <!--C3P0-->
    <dependency>
      <groupId>c3p0</groupId>
      <artifactId>c3p0</artifactId>
      <version>0.9.1.2</version>
    </dependency>
  </dependencies>

  <build>
    <finalName>TestJDBC-demo</finalName>
    <pluginManagement><!-- lock down plugins versions to avoid using Maven defaults (may be moved to parent pom) -->
      <plugins>
        <plugin>
          <artifactId>maven-clean-plugin</artifactId>
          <version>3.1.0</version>
        </plugin>
        <!-- see http://maven.apache.org/ref/current/maven-core/default-bindings.html#Plugin_bindings_for_war_packaging -->
        <plugin>
          <artifactId>maven-resources-plugin</artifactId>
          <version>3.0.2</version>
        </plugin>
        <plugin>
          <artifactId>maven-compiler-plugin</artifactId>
          <version>3.8.0</version>
        </plugin>
        <plugin>
          <artifactId>maven-surefire-plugin</artifactId>
          <version>2.22.1</version>
        </plugin>
        <plugin>
          <artifactId>maven-war-plugin</artifactId>
          <version>3.2.2</version>
        </plugin>
        <plugin>
          <artifactId>maven-install-plugin</artifactId>
          <version>2.5.2</version>
        </plugin>
        <plugin>
          <artifactId>maven-deploy-plugin</artifactId>
          <version>2.8.2</version>
        </plugin>
      </plugins>
    </pluginManagement>
  </build>
</project>

封装对于任何数据表的CRUD操作：

DAOHelper：

public class DAOHelper {

    /**
     * 通用增删改
     * @param conn
     * @param sql
     * @param objs
     * @return
     */
    public static boolean execUpdate(Connection conn, String sql, Object... objs){
        PreparedStatement ps = null;
        try {
            ps = conn.prepareStatement(sql);
            for (int i = 0; i < objs.length; i++) {
                ps.setObject(i + 1, objs[i]);
            }
            int i = ps.executeUpdate();
            return i > 0 ? true : false;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    /**
     * 通用查询操作
     * @param conn
     * @param sql
     * @param call
     * @param objs
     * @param <T>
     * @return
     */
    public static <T> List<T> execQuery(Connection conn, String sql, CallBack<T> call, Object... objs){
        PreparedStatement ps = null;
        try {
            ps = conn.prepareStatement(sql);
            for (int i = 0; i < objs.length; i++) {
                ps.setObject(i + 1, objs[i]);
            }
            return call.getDatas(ps.executeQuery());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
    public static <T> T execQueryOne(Connection conn, String sql, CallBack<T> call, Object... objs){
        PreparedStatement ps = null;
        try {
            ps = conn.prepareStatement(sql);
            for (int i = 0; i < objs.length; i++) {
                ps.setObject(i + 1, objs[i]);
            }
            ResultSet rs = ps.executeQuery();
            return call.getData(rs);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
    public interface CallBack<T>{
        default List<T> getDatas(ResultSet rs){
            return null;
        }
        default T getData(ResultSet rs){
            return null;
        }
    }
    public static abstract class CallBack2<T>{
        public List<T> getDatas(ResultSet rs){
            return null;
        }
        public T getData(ResultSet rs){
            return null;
        }
    }
}

BaseDAO.java：

public interface BaseDAO<T>  {
    public boolean insert(Connection conn, T t);

    public boolean delete(Connection conn, T t);

    public boolean update(Connection conn, T t);

    public T findById(Connection conn, T t);

    public List<T> findAll(Connection conn, int pageSize, int currentPage);

    public int getCount(Connection conn);
}

UserDAO：

public class UserDAO implements BaseDAO<User> {

    @Override
    public boolean insert(Connection conn, User user) {
        return DAOHelper.execUpdate(conn, "insert into user (user_name) values(?)", user.getUser_name());
    }

    /**
     * 批处理
     * @param conn
     */
    public void batch2(Connection conn){
        PreparedStatement ps = null;
        try {
            ps = conn.prepareStatement("insert into user(user_name) values(?)");
            for (int i = 0; i < 30; i++) {
                ps.setString(1, "test" + i);
                //先加入缓存
                ps.addBatch();
                if (i % 10 == 0) {
                    ps.executeBatch();  //先执行一波
                    ps.clearBatch();    //清空缓冲区
                }
                ps.executeBatch();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }


    @Override
    public boolean delete(Connection conn, User user) {
        return false;
    }

    @Override
    public boolean update(Connection conn, User user) {
        return false;
    }

    @Override
    public User findById(Connection conn, User user) {
        return DAOHelper.execQueryOne(conn, "select user_id, user_name, create_time from user where user_id = ?", new DAOHelper.CallBack<User>() {
            @Override
            public User getData(ResultSet rs) {
                ResultSetMetaData rsmd = null;
                try {
                    if (rs.next()) {
                        Map<String, Object> map = new HashMap();
                        rsmd = rs.getMetaData();
                        int count = rsmd.getColumnCount();//获得列数
                        String label;
                        for (int i = 0; i < count; i++) {
                            label = rsmd.getColumnLabel(i + 1);//获取列名 可能是别名
                            map.put(label, rs.getObject(label));
                        }
                        Class clz = user.getClass();
                        for (Map.Entry<String, Object> entry : map.entrySet()){
                            String Key = entry.getKey();
                            Object Value = entry.getValue();
                            //根据属性名获取set方法名字
                            String method_set_name = "set" + Key.substring(0,1).toUpperCase() + Key.substring(1);
                            //根据set方法的名字和属性类型获取set方法
                            Method method_set = clz.getMethod(method_set_name, clz.getDeclaredField(Key).getType());
                            //如果有关联直接去map中取数据，否则运行set方法将value值设给属性
                            Class type = method_set.getParameterTypes()[0];
                            //根据属性的类型将String转换为相应类型值后再set
                            method_set.invoke(user, TypeUtils.getValue(type.getName(), entry.getValue().toString()));
                        }
                        return user;
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                } catch (NoSuchMethodException e) {
                    e.printStackTrace();
                } catch (NoSuchFieldException e) {
                    e.printStackTrace();
                } catch (IllegalAccessException e) {
                    e.printStackTrace();
                } catch (InvocationTargetException e) {
                    e.printStackTrace();
                }
                return null;
            }
        }, user.getUser_id());
    }

    @Override
    public List<User> findAll(Connection conn, int pageSize, int currentPage) {
        return null;
    }

    @Override
    public int getCount(Connection conn) {
        return 0;
    }
}


//简单的实现：

    /**
     * 分页的实现
     * @param conn
     * @param pageSize      第几页
     * @param currentPage   每页数据条数
     * @return
     */
    @Override
    public List<Emp> findAll(Connection conn, int pageSize, int currentPage) {
        return DAOHelper.execQuery(conn, "select user_id,user_name,sex,mobile,password,registerTime from emp limit ?,?", new DAOHelper.CallBack<Emp>() {
            @Override
            public List<Emp> getDatas(ResultSet rs)  {
                List<Emp> list = new ArrayList<>();
                ResultSetMetaData rsmd = null;
                Map map = new HashMap();
                try {
                    rsmd = rs.getMetaData();
                    int count = rsmd.getColumnCount();
                    while (rs.next()){
                        String label;
                        for (int i = 0; i < count; i++) {
                            label = rsmd.getColumnLabel(i+1);
                            map.put(label, rs.getObject(label));
                        }
                        Emp e = new Emp();
                        e.setUser_id(map.get("user_id") + "");
                        e.setUser_name(map.get("user_name") + "");
                        e.setSex(map.get("sex") + "");
                        e.setMobile(map.get("mobile") + "");
                        e.setPassword(map.get("password") + "");
                        e.setRegisterTime((Timestamp)map.get("registerTime"));
                        list.add(e);
                    }
                }catch (SQLException e) {
                    e.printStackTrace();
                }
                return list;
            }
        }, (currentPage-1)*pageSize, pageSize);
    }

    @Override
    public int getCount(Connection conn) {
        String sql = "select count(user_id) from emp";
        return DBUtils.execQueryOne(conn, sql, new DBUtils.CallBack<Integer>() {
            @Override
            public Integer getData(ResultSet rs) {
                try {
                    if (rs.next())
                        return rs.getInt(1);
                } catch (SQLException e) {
                    e.printStackTrace();
                } finally {
                    try {
                        if (rs != null)
                            rs.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
                return 0;
            }
        });
    }

    public static void getReasonableData(Connection conn, int currentPage, int pageSize){
        int totalNum = new EmpDAO().getCount(conn);   //总记录条数
        int totalPage = 0;   //总页数

        //计算总列数
        if (totalNum % pageSize == 0) {
            totalPage = totalNum / pageSize;
        }else {
            totalPage = totalPage / pageSize + 1;
        }
        //防止无限上一页
        if (currentPage < 1) {
            currentPage = 1;
        }
        //防止无限下一页
        if (currentPage > totalPage) {
            currentPage = totalPage;
        }

    }

TypeUtils.java：

public class TypeUtils {
    public static Object getValue(String type, String value){
        if ("int".equals(type)){
            return Integer.parseInt(value);
        }else if ("short".equals(type)){
            return Short.parseShort(value);
        }else if ("long".equals(type)){
            return Long.parseLong(value);
        }else if ("float".equals(type)){
            return Float.parseFloat(value);
        }else if ("double".equals(type)){
            return Double.parseDouble(value);
        }else if ("char".equals(type)){
            return value.charAt(0);
        }else if ("byte".equals(type)){
            return Byte.parseByte(value);
        }else if ("boolean".equals(type)){
            return Boolean.parseBoolean(value);
        }else if ("java.util.Date".equals(type)){
            try {
                return new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS").parse(value);
            } catch (ParseException e) {
                e.printStackTrace();
            }
            return null;
        }else {
            return value;
        }
    }
}

TestDemo.java：

public class TestDemo1 {

    @org.junit.Before
    public void init(){
    }
    /**
     * 获取连接，配置连接池
     * @throws SQLException
     */
    @org.junit.Test
    public void getConnByJDBC() throws SQLException {
        System.out.println(DBConnection.getConn());
    }

    @org.junit.Test
    public void getConnByC3P0() throws SQLException {
        System.out.println(DBConnectionC3P0.getConn());
    }

    @org.junit.Test
    public void insert() throws SQLException {
        Connection connection = DBConnectionC3P0.getConn();
        boolean flag = DAOHelper.execUpdate(connection, "insert into user (user_name) values(?)", "老王");
        System.out.println(flag);
    }

    @org.junit.Test
    public void queryList() throws SQLException {
        String time = "2019-07-14 15:36:12.0";
        try {
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");
            Date myString1 = sdf.parse(time);
            System.out.println(myString1);
        } catch (ParseException e) {
            e.printStackTrace();
        }
    }

    @org.junit.Test
    public void queryOne() throws SQLException {
        User u = new User();
        u.setUser_id(1);
        u = new UserDAO().findById(DBConnectionC3P0.getConn(), u);
        System.out.println(u);
    }
}

jQuery ajax模板：

https://blog.csdn.net/qq_28905427/article/details/81094269

原生js的ajax：https://blog.csdn.net/u014802525/article/details/82493166

$.ajax({
    url:'',
    type:'POST', //GET
    async:true,    //或false,是否异步
    data:{
        name:'llc',
        age:22
    },
    timeout:5000,    //超时时间
    dataType:'json',    //返回的数据格式：json/xml/html/script/jsonp/text
    beforeSend:function(xhr){
        console.log(xhr)
        console.log('发送前')
    },
    success:function(data,textStatus,jqXHR){
        console.log(data)；
    },
    error:function(xhr,textStatus){
        console.log('错误'，xhr.responseText)；
        console.log(xhr)；
        console.log(textStatus)；
    }
})

post提交：

<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
    <title>Title</title>
    <form action="LoginServlet" method="post">
        username:<input type="text" name="uname" >
        password:<input type="password" name="upass" ><br>
        <button type="button">登录</button> 这种不提交<br>
        <button>登录</button> 这种提交<br>
        <input type="button" value="登录" />这种不提交<br>
        <input type="submit" value="登录" />这种提交<br>
    </form>
</head>
<body>

</body>
</html>

LoginServlet：

public class LoginServlet extends HttpServlet {

    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        System.out.println("get提交");
        doPost(request,response);
    }

    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        System.out.println("post提交");

        //设置请求头的编码方式
        request.setCharacterEncoding("utf-8");
        //设置响应数据流编码
        response.setCharacterEncoding("utf-8");
        //设置响应头的内容（包括响应数据格式，编码格式）
        response.setContentType("text/html;charset=utf-8");

        String uname = request.getParameter("uname");
        String upass = request.getParameter("upass");
        System.out.println(uname + "--" + upass);
        PrintWriter out = response.getWriter();
        out.println("<script>alert('中文n');history.back();</script>");
        out.flush();

    }
}

原生ajax调用：

<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
    <title>Title</title>
</head>
<body>
    <form>
        <div id="msg"></div>
        <input type="text" name="username" id="username" onblur="checkName(this.value)">
        <button type="button" onclick="checkName()">检查账号是否可用</button>
    </form>

    <script>
        //检查
        function checkName(name) {
            /*var input_name = document.getElementById('username');
            var name = input_name.value;*/
            ajaxGet('check?uname='+name, function(data){
                var msgBox = document.getElementById('msg');
                alert(data);
                if (data == -1){
                    //账号不可用
                    msgBox.style.color = '#f00';
                    msgBox.innerHTML = '账号已被注册';
                }else {
                    //账号可用
                    msgBox.style.color = '#0f0';
                    msgBox.innerHTML = '账号可以使用';
                }
            });
        }
        //ajax
        function ajaxGet(url, callback) {
            //声明XMLHttpRequest
            var xhr;
            //判断浏览器获取XMLHttpRequest
            if (window.XMLHttpRequest){
                xhr = new XMLHttpRequest();
            }else {
                //IE5,IE6支持ActiveX插件
                xhr = new ActiveXObject('microsoft.XMLHTTP');
            }
            //打开连接  1.请求方式 2.服务端的资源地址 3.是否为异步提交
            xhr.open('get', url, true);
            //发送请求 当请求方式为post时，send方法需要写参数
            xhr.send();
            //当请求状态发生改变时，执行回调
            xhr.onreadystatechange = function(){
                //readyState:请求状态，status服务器响应状态
                if (xhr.readyState == 4 && xhr.status == 200){
                    //获取服务端的响应数据
                    var data = xhr.responseText;
                    //将字符串类型的json数据转换为js对象
                    data = JSON.parse(data);
                    //执行回调
                    callback(data);
                }
            }
        }
    </script>
</body>
</html>

check：

@WebServlet("/check")
public class CheckNameServlet extends HttpServlet {
    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        doPost(req,resp);
    }

    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        response.setCharacterEncoding("utf-8");
        String name = request.getParameter("uname");
        System.out.println("name:"+name);
        PrintWriter out = response.getWriter();
        if ("wan".equals(name)){
            out.print("-1");
        }else {
            out.print("1");
        }
        out.flush();
    }
}

实例：ajax提交

function fn3(){
    $.ajax({
        url:"/AjaxDemo/ajaxServlet2",
	async:true,
	type:"post",
	data:{"name":"Stephen chow","age":"55"},
	success:function(data){
	    alert(data.name);
	},
	error:function(){
	    alert("请求失败");
	},
	dataType:"json"
    });
}

后台代码：

(2)AjaxServlet.java
package com.shu.hj;
 
import java.io.IOException;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
 
/**
 * Servlet implementation class AjaxServlet2
 */
public class AjaxServlet2 extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
   
 
	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
		//response.getWriter().append("Served at: ").append(request.getContextPath());
		String name = request.getParameter("name");
		String age = request.getParameter("age");
		System.out.println("name: "+name+" age: "+age);
	//当前端页面数据提交过来后，服务器端需要返回信息
		//response.getWriter().write("success...");
	//此时返回一个字符串"success..."，考虑一下前端如何接收
	//在回调函数中接收
		
	/*
	 如果返回格式要求为json，则服务器端Java代码只能返回一个json格式的字符串
	 {"name":"tom","age":"26"}
	 * */
		//System.out.println("{"name":"tom","age":"26"}");
		//以上直接将json格式字符串放入其中会报错，会发现花括号里外都是双引号
		//如果将内部改为单引号System.out.println("{'name':'tom','age':'26'}");
		//此时同样会报错，{'test': 1} （使用了单引号而不是双引号）会被识别为畸形json格式
		System.out.println("{\"name\":\"tom\",\"age\":\"26\"}");//使用转义字符
                response.getWriter().write("{\"name\":\"tom\",\"age\":\"26\"}");
注意：回调函数中的数据是用response.getWriter().write()传递的，不是System.out.println().
	}
 
	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
		doGet(request, response);
	}
 
}

web.xml：

<!DOCTYPE web-app PUBLIC
 "-//Sun Microsystems, Inc.//DTD Web Application 2.3//EN"
 "http://java.sun.com/dtd/web-app_2_3.dtd" >

<web-app>
  <display-name>Archetype Created Web Application</display-name>

  <filter>
    <filter-name>HelloFilter</filter-name>
    <filter-class>com.wan.HelloFilter</filter-class>
  </filter>
  <filter-mapping>
    <filter-name>HelloFilter</filter-name>
    <url-pattern>/main.jsp</url-pattern>
  </filter-mapping>

  <!--配置servlet-->
  <servlet>
    <servlet-name>HelloServlet</servlet-name>
    <servlet-class>com.wan.HelloServlet</servlet-class>
  </servlet>
  <servlet>
    <servlet-name>LoginServlet</servlet-name>
    <servlet-class>com.wan.LoginServlet</servlet-class>
  </servlet>



  <servlet-mapping>
    <servlet-name>HelloServlet</servlet-name>
    <url-pattern>/HelloServlet</url-pattern>
  </servlet-mapping>
  <servlet-mapping>
    <servlet-name>LoginServlet</servlet-name>
    <url-pattern>/LoginServlet</url-pattern>
  </servlet-mapping>

  <error-page>
    <error-code>404</error-code>
    <location>/404.jsp</location>
  </error-page>
  <error-page>
    <exception-type>java.lang.NullPointerException</exception-type>
    <location>/Exception</location>
  </error-page>


</web-app>

丸锃卉

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
20190813JDBC

maven settings.xml<localRepository>F:\Java\Apache\repository</localRepository><mirror> <id>alimaven</id> <name>aliyun maven</name> <...
复制链接

扫一扫