DBUtils 练习

最新推荐文章于 2022-06-25 18:03:25 发布

南北极之间

最新推荐文章于 2022-06-25 18:03:25 发布

阅读量496

点赞数

分类专栏： java

本文链接：https://blog.csdn.net/qq_22182989/article/details/80523832

版权

java 专栏收录该内容

34 篇文章 3 订阅

订阅专栏

sql

CREATE TABLE `visitor` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `Name` varchar(1000) NOT NULL,
  `Email` varchar(1000) NOT NULL,
  `Status` int(11) NOT NULL DEFAULT '1',
  `CreateTime` datetime DEFAULT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8;

INSERT INTO `cxltest2`.`visitor` (`Id`, `Name`, `Email`, `Status`, `CreateTime`) VALUES ('1', 'lili', '123@163.com', '1', '2018-05-31 13:31:35');
INSERT INTO `cxltest2`.`visitor` (`Id`, `Name`, `Email`, `Status`, `CreateTime`) VALUES ('2', 'ccc', '1346@qq.com', '1', '2018-05-31 13:31:54');
INSERT INTO `cxltest2`.`visitor` (`Id`, `Name`, `Email`, `Status`, `CreateTime`) VALUES ('3', 'ss', 'ss', '1', '2018-05-31 13:32:53');
INSERT INTO `cxltest2`.`visitor` (`Id`, `Name`, `Email`, `Status`, `CreateTime`) VALUES ('4', 'grg', 'dsf@qq.com', '1', '2018-05-31 13:33:24');
INSERT INTO `cxltest2`.`visitor` (`Id`, `Name`, `Email`, `Status`, `CreateTime`) VALUES ('5', 'dsgd', 'ty3@qq.com', '1', '2018-05-31 13:33:39');

INSERT INTO `cxltest2`.`visitor` (`Id`, `Name`, `Email`, `Status`, `CreateTime`) VALUES ('7', 'lucy', 'lucy@163.com', '1', '2018-05-31 13:39:36');

1 lili 123@163.com 1 2018-05-31 13:31:35
2 ccc 1346@qq.com 1 2018-05-31 13:31:54
3 ss ss 1 2018-05-31 13:32:53
4 grg dsf@qq.com 1 2018-05-31 13:33:24
5 dsgd ty3@qq.com 1 2018-05-31 13:33:39

7 lucy lucy@163.com 1 2018-05-31 13:39:36

===========================

Visitor

实体类

package david.apache.model;

import java.text.SimpleDateFormat;
import java.util.Date;

public class Visitor {

    private int id;
    private String name;
    private String email;
    private int status;
    private Date createTime;

    public Visitor() {
        // TODO Auto-generated constructor stub
        setCreateTime(new Date());
    }

    public Visitor(String name, String email) {
        this.setName(name);
        this.setEmail(email);
        this.setStatus(1);
        this.setCreateTime(new Date());
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public int getStatus() {
        return status;
    }

    public void setStatus(int status) {
        this.status = status;
    }

    public Date getCreateTime() {
        return createTime;
    }

    public void setCreateTime(Date createTime) {
        this.createTime = createTime;
    }

    @Override
    public String toString() {
        // TODO Auto-generated method stub
        return String.format("{Id: %d, Name: %s, Email: %s, CreateTime: %s}", getId(), getName(), getEmail(),
                new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(getCreateTime()));
    }
}

===========

demo

test1 如下

import david.apache.model.Visitor;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.*;

import java.math.BigInteger;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;

public class test1 {
    private static Connection getConnection() {
        Connection conn = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = (Connection) DriverManager.getConnection("jdbc:mysql://localhost:3306/cxltest2?useUnicode=true&characterEncoding=utf8", "root", "123456");
        } catch (Exception e) {
            e.printStackTrace();
        }
        return (Connection) conn;
    }
    /*
     * 新增Visitor, ScalarHandler的demo
     */
    public static void insertVisitor(Visitor visitor) {
        Connection conn = (Connection) getConnection();
        QueryRunner qr = new QueryRunner();
        String sql = "insert into visitor (Name, Email, Status, CreateTime) values (?, ?, ?, ?)";
        try {
            int count = qr.update(conn, sql, visitor.getName(), visitor.getEmail(), 1, new Date());
            BigInteger newId = (BigInteger) qr.query(conn, "select last_insert_id()", new ScalarHandler<BigInteger>(1));
            visitor.setId(Integer.valueOf(String.valueOf(newId)));
            System.out.println("新增" + count + "条数据=>Id：" + newId);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

//查询
    public static Visitor retrieveVisitor(int id) {
        Connection conn = (Connection) getConnection();
        Visitor visitor = null;
        QueryRunner qr = new QueryRunner();
        String sql = "select * from visitor where status>0 and id=?";
        try {
            visitor = (Visitor) qr.query(conn, sql, new BeanHandler<Visitor>(Visitor.class), id);
            System.out.println(visitor);
            return visitor;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return visitor;
    }
    //删除
    public static void deleteVisitor(int id) {
        Connection conn = getConnection();
        QueryRunner qr = new QueryRunner();
        String sql = "delete from visitor where status>0 and id=?";
        try {
            int count = qr.update(conn, sql, id);
            System.out.println("删除" + count + "条数据。");
        } catch (SQLException e) {
            // TODO: handle exception
            e.printStackTrace();
        }
    }

    //更新
    public static void updateVisitor(int id) {
        Visitor visitor = retrieveVisitor(id);
        System.out.println("更新前：" + visitor);
        Connection conn = getConnection();
        String updateFieldStr = visitor.getName();
        QueryRunner qr = new QueryRunner();
        String sql = "update visitor set Name = ?, Email = ?, Status = ?, CreateTime = ? where status>0 and Id = ?";
        if (updateFieldStr.contains("updated")) {
            updateFieldStr = updateFieldStr.substring(0, updateFieldStr.indexOf("updated"));
        } else {
            updateFieldStr = updateFieldStr + "updated";
        }
        visitor.setName(updateFieldStr);
        try {
            int count = qr.update(conn, sql, new Object[] { visitor.getName(), visitor.getName(), visitor.getStatus(),
                    visitor.getCreateTime(), visitor.getId() });
            System.out.println("更新了" + count + "条数据");
            System.out.println("更新后：" + visitor);
        } catch (SQLException e) {
            // TODO: handle exception
            e.printStackTrace();
        }
    }

    public static void getVisitorList() {
        Connection conn = getConnection();
        QueryRunner qr = new QueryRunner();
        String sql = "select * from visitor where status>0";
        try {
            List<Visitor> ls = qr.query(conn, sql, new BeanListHandler<Visitor>(Visitor.class));
            for (Visitor visitor : ls) {
                System.out.println(visitor);
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    public static void getVisitWithMap(int id) {
        Connection conn = getConnection();
        QueryRunner qr = new QueryRunner();
        String sql = "select * from visitor where status>0 and id=?";
        try {
            Map<String, Object> map = qr.query(conn, sql, new MapHandler(), id);
            Integer visitorId = Integer.valueOf(map.get("Id").toString());
            String visitorName = map.get("Name").toString();
            String visitorEmail = map.get("Email").toString();
            Integer visitorStatus = Integer.valueOf(map.get("Status").toString());
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            Date visitorCreateTime = sdf.parse(map.get("CreateTime").toString());
            Visitor visitor = new Visitor(visitorName, visitorEmail);
            visitor.setId(visitorId);
            visitor.setStatus(visitorStatus);
            visitor.setCreateTime(visitorCreateTime);
            System.out.println(visitor);
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        }
    }

    public static void getVisitWithMapLs() {
        Connection conn = getConnection();
        QueryRunner qr = new QueryRunner();
        String sql = "select * from visitor where status>0";
        try {
            List<Map<String, Object>> mapLs = qr.query(conn, sql, new MapListHandler());
            for (Map<String, Object> map : mapLs) {
                Integer visitorId = Integer.valueOf(map.get("Id").toString());
                String visitorName = map.get("Name").toString();
                String visitorEmail = map.get("Email").toString();
                Integer visitorStatus = Integer.valueOf(map.get("Status").toString());
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                Date visitorCreateTime = sdf.parse(map.get("CreateTime").toString());
                Visitor visitor = new Visitor(visitorName, visitorEmail);
                visitor.setId(visitorId);
                visitor.setStatus(visitorStatus);
                visitor.setCreateTime(visitorCreateTime);
                System.out.println(visitor);
            }
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        }
    }
    public static void main(String[] args) {
        //查询
        retrieveVisitor(1);
        //新增
        Visitor visitor = new Visitor();
        visitor.setName("lucy");
        visitor.setEmail("lucy@163.com");
        visitor.setStatus(1);
        visitor.setCreateTime(new Date());
        insertVisitor(visitor);
        //删除
        deleteVisitor(6);
        //更新
        updateVisitor(6);

//        getVisitorList();

//        getVisitWithMap(1);
//        getVisitWithMapLs();
    }
}

=======================

pom

dbutils的jar包，手动添加

    <dependency>
      <groupId>commons-dbutils</groupId>
      <artifactId>commons-dbutils</artifactId>
      <version>1.7</version>
    </dependency>

完整pom没有整理

<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/maven-v4_0_0.xsd">
  <modelVersion>4.0.0</modelVersion>
  <groupId>com.iflytek</groupId>
  <artifactId>user-admin</artifactId>
  <packaging>war</packaging>
  <version>0.0.1-SNAPSHOT</version>
  <name>user-admin Maven Webapp</name>
  <url>http://maven.apache.org</url>
  <!-- 节点配置属性 -->
  <properties>
    <java.version>1.7</java.version>
    <junit.version>3.8.1</junit.version>
    <mysql.version>5.1.30</mysql.version>
    <jackson.version>2.4.5</jackson.version>
    <druid.version>1.0.5</druid.version>
    <springframework.version>3.2.4.RELEASE</springframework.version>
    <mybatis.version>3.2.8</mybatis.version>
    <mybatis-spring.version>1.2.2</mybatis-spring.version>
  </properties>
  <dependencies>
    <!-- test -->
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>${junit.version}</version>
      <scope>test</scope>
    </dependency>

    <dependency>
      <groupId>com.alibaba</groupId>
      <artifactId>druid</artifactId>
      <version>${druid.version}</version>
    </dependency>

    <!-- spring相关配置 -->
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-context</artifactId>
      <version>${springframework.version}</version>
      <exclusions>
        <!-- Exclude Commons Logging in favor of SLF4j -->
        <exclusion>
          <groupId>commons-logging</groupId>
          <artifactId>commons-logging</artifactId>
        </exclusion>
      </exclusions>
    </dependency>

    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-test</artifactId>
      <version>${springframework.version}</version>
      <exclusions>
        <!-- Exclude Commons Logging in favor of SLF4j -->
        <exclusion>
          <groupId>commons-logging</groupId>
          <artifactId>commons-logging</artifactId>
        </exclusion>
      </exclusions>
    </dependency>

    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-webmvc</artifactId>
      <version>${springframework.version}</version>
    </dependency>

    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-tx</artifactId>
      <version>${springframework.version}</version>
    </dependency>


    <!-- @Inject -->
    <dependency>
      <groupId>javax.inject</groupId>
      <artifactId>javax.inject</artifactId>
      <version>1</version>
    </dependency>
    <!-- Servlet -->
    <dependency>
      <groupId>javax.servlet</groupId>
      <artifactId>servlet-api</artifactId>
      <version>2.5</version>
      <scope>provided</scope>
    </dependency>
    <dependency>
      <groupId>javax.servlet.jsp</groupId>
      <artifactId>jsp-api</artifactId>
      <version>2.1</version>
      <scope>provided</scope>
    </dependency>
    <!-- json convert -->
    <dependency>
      <groupId>com.fasterxml.jackson.core</groupId>
      <artifactId>jackson-core</artifactId>
      <version>${jackson.version}</version>
    </dependency>
    <dependency>
      <groupId>org.codehaus.jackson</groupId>
      <artifactId>jackson-mapper-asl</artifactId>
      <version>1.9.13</version>
    </dependency>
    <dependency>
      <groupId>com.fasterxml.jackson.core</groupId>
      <artifactId>jackson-databind</artifactId>
      <version>${jackson.version}</version>
      <exclusions>
        <exclusion>
          <artifactId>jackson-annotations</artifactId>
          <groupId>com.fasterxml.jackson.core</groupId>
        </exclusion>
      </exclusions>
    </dependency>
    <dependency>
      <groupId>com.fasterxml.jackson.core</groupId>
      <artifactId>jackson-annotations</artifactId>
      <version>${jackson.version}</version>
    </dependency>
    <!-- mysql -->
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>${mysql.version}</version>
    </dependency>
    <!-- 连接池 -->
    <dependency>
      <groupId>commons-dbcp</groupId>
      <artifactId>commons-dbcp</artifactId>
      <version>1.4</version>
    </dependency>

    <dependency>
      <groupId>org.mybatis</groupId>
      <artifactId>mybatis-spring</artifactId>
      <version>${mybatis-spring.version}</version>
    </dependency>

    <dependency>
      <groupId>org.mybatis</groupId>
      <artifactId>mybatis</artifactId>
      <version>${mybatis.version}</version>
    </dependency>

    <dependency>
      <groupId>com.alibaba</groupId>
      <artifactId>fastjson</artifactId>
      <version>1.1.41</version>
    </dependency>

    <dependency>
      <groupId>commons-beanutils</groupId>
      <artifactId>commons-beanutils</artifactId>
      <version>1.9.2</version>
      <exclusions>
        <exclusion>
          <artifactId>commons-logging</artifactId>
          <groupId>commons-logging</groupId>
        </exclusion>
      </exclusions>
    </dependency>
    <!-- jstl -->
    <dependency>
      <groupId>javax.servlet</groupId>
      <artifactId>jstl</artifactId>
      <version>1.2</version>
    </dependency>
    <dependency>
      <groupId>org.aspectj</groupId>
      <artifactId>aspectjweaver</artifactId>
      <version>1.8.9</version>
    </dependency>
    <!--srping redis -->
    <dependency>
      <groupId>org.springframework.data</groupId>
      <artifactId>spring-data-redis</artifactId>
      <version>1.6.2.RELEASE</version>
    </dependency>
    <dependency>
      <groupId>commons-fileupload</groupId>
      <artifactId>commons-fileupload</artifactId>
      <version>1.2.2</version>
    </dependency>

    <dependency>
      <groupId>commons-dbutils</groupId>
      <artifactId>commons-dbutils</artifactId>
      <version>1.7</version>
    </dependency>
    <dependency>
      <groupId>c3p0</groupId>
      <artifactId>c3p0</artifactId>
      <version>0.9.1.2</version>
    </dependency>
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>4.12</version>
    </dependency>
  </dependencies>


  <build>
    <finalName>user-admin</finalName>
    <plugins>
      <plugin>
        <groupId>org.apache.maven.plugins</groupId>
        <artifactId>maven-compiler-plugin</artifactId>
        <version>2.5.1</version>
        <configuration>
          <source>1.7</source>
          <target>1.7</target>
          <encoding>UTF-8</encoding>
          <compilerArgument>-Xlint:all</compilerArgument>
          <showWarnings>true</showWarnings>
          <showDeprecation>true</showDeprecation>
        </configuration>
      </plugin>
      <!-- jsp 预编译插件 -->
      <plugin>
        <groupId>org.mortbay.jetty</groupId>
        <artifactId>jetty-maven-plugin</artifactId>
        <version>8.1.16.v20140903</version>
        <configuration>
          <webAppConfig>
            <contextPath>/user-admin</contextPath>
          </webAppConfig>
        </configuration>
      </plugin>
    </plugins>
  </build>
</project>

待整理

南北极之间

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
打赏
0
评论
DBUtils 练习

sqlCREATE TABLE `visitor` ( `Id` int(11) NOT NULL AUTO_INCREMENT, `Name` varchar(1000) NOT NULL, `Email` varchar(1000) NOT NULL, `Status` int(11) NOT NULL DEFAULT '1', `CreateTime` datetime ...
复制链接

扫一扫