(*´∇｀*)白筱の猫舍

数据库连接层

  • ~9.33K 字
  1. 1. jdbcutil.java
  2. 2. JdbcutilTest.java 测试类
  3. 3. Basedao.java
  4. 4. User.java
  5. 5. UserDaoImpl.java
  6. 6. UserDao.interface 注释
  7. 7. UserDaoTest

jdbcutil.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
package com.white.utils;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;

/*获取连接*/
public class Jdbcutil {

    private static  DruidDataSource dataSource;
    static {
        try {
            Properties properties =new Properties();
            //读取jdbc.properties属性配置文件
            InputStream inputStream = Jdbcutil.class.getClassLoader().getResourceAsStream("jdbc.properties");
            //从流中价值数据
            properties.load(inputStream);
            //创建了数据库连接池
            dataSource= (DruidDataSource) DruidDataSourceFactory.createDataSource(properties);
           
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args) {
//主方法测试
    }
    public static Connection getConnection()
    {    //如果返回null说明获取连接失败，有值就是成功
        Connection conn =null;
        
        try {
            conn =dataSource.getConnection();
        } catch (Exception throwables) {
            throwables.printStackTrace();
        }
        return conn;
    }
    public static void close(Connection conn){
/*关闭连接*/
        if (conn!=null){
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }

}

JdbcutilTest.java 测试类

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
package com.white.text;

import com.white.utils.Jdbcutil;
import org.junit.Test;

import java.sql.Connection;

public class JdbcUtilsTest {
    @Test
    public void testJdbcUtil(){
        for (int i = 0; i<100;i++)
        {
            Connection connection = Jdbcutil.getConnection();
            System.out.println(connection);
            Jdbcutil.close(connection);//及时释放
        }
    }
}

Basedao.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
package com.white.dao.impl;


import com.white.utils.Jdbcutil;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

public  abstract class BaseDao {//服用代码不需要实例

    private QueryRunner queryRunner = new QueryRunner();
    //update来执行/增删改查
    public int update(String sql,Object ... args){
        Connection connection = Jdbcutil.getConnection();
        try {
           return queryRunner.update(connection,sql,args);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            Jdbcutil.close(connection);
        }
        return -1;
        //返回-1表示失败，返回其他表示影响的行数
    }

    public <T> T queryForOne(Class<T>type,String sql,Object ... args){
        Connection con = Jdbcutil.getConnection();

        try {
            return queryRunner.query(con,sql,new BeanHandler<T>(type),args);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            Jdbcutil.close(con);
        }
        return null;
    }

    //查询返回多个java
    public <T>List<T> queryForyList(Class<T> type,String sql,Object ... args){
        Connection con = Jdbcutil.getConnection();

        try {
            return queryRunner.query(con,sql,new BeanListHandler<T>(type),args);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            Jdbcutil.close(con);
        }
        return null;
    }

    public  Object queryForSingleValue(String sql,Object ... args){
        Connection conn = Jdbcutil.getConnection();
        try {
            return queryRunner.query(conn, sql,new ScalarHandler(),args);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        finally {
            Jdbcutil.close(conn);
        }
        return null;
    }

}

User.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
package com.white.dao.impl;


import com.white.utils.Jdbcutil;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

public  abstract class BaseDao {//服用代码不需要实例

    private QueryRunner queryRunner = new QueryRunner();
    //update来执行/增删改查
    public int update(String sql,Object ... args){
        Connection connection = Jdbcutil.getConnection();
        try {
           return queryRunner.update(connection,sql,args);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            Jdbcutil.close(connection);
        }
        return -1;
        //返回-1表示失败，返回其他表示影响的行数
    }

    public <T> T queryForOne(Class<T>type,String sql,Object ... args){
        Connection con = Jdbcutil.getConnection();

        try {
            return queryRunner.query(con,sql,new BeanHandler<T>(type),args);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            Jdbcutil.close(con);
        }
        return null;
    }

    //查询返回多个java
    public <T>List<T> queryForyList(Class<T> type,String sql,Object ... args){
        Connection con = Jdbcutil.getConnection();

        try {
            return queryRunner.query(con,sql,new BeanListHandler<T>(type),args);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            Jdbcutil.close(con);
        }
        return null;
    }

    public  Object queryForSingleValue(String sql,Object ... args){
        Connection conn = Jdbcutil.getConnection();
        try {
            return queryRunner.query(conn, sql,new ScalarHandler(),args);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        finally {
            Jdbcutil.close(conn);
        }
        return null;
    }

}

UserDaoImpl.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
package com.white.dao.impl;

import com.white.dao.UserDao;
import com.white.pojo.User;

public class UserDaoImpl extends BaseDao implements UserDao {
    @Override
    public User queruserByUsername(String username) {
        String sql = "select `id`,`username`,`password`,`call` from t_user where username = ?";

        return queryForOne(User.class,sql,username);
    }

    @Override
    public User queruserByUsernameAndPassword(String username, String password) {
        String sql = "select `id`,`username`,`password`,`call` from t_user where username = ? and password = ?";

        return queryForOne(User.class,sql,username,password);
    }

    @Override
    public int saveUser(User user) {
        String sql = "INSERT INTO `t_user`(`username`,`password`,`call`) VALUES (?,?,?);\n";
        return update(sql,user.getUsername(),user.getPassword(),user.getCall());
    }
}

UserDao.interface 注释

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
package com.white.dao;

import com.white.pojo.User;
public interface UserDao {

    /**
     *  @param username 用户名
    * @return 如果返回null，说明没有这个用户，反之亦然
    * */

    public User queruserByUsername(String username);
    public User queruserByUsernameAndPassword(String username,String password);
/*
* 报存用户信息
* @param user
* @return 返回-1表示操作失败*/
    public int saveUser(User user);


}

UserDaoTest

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
package com.white.text;

import com.white.dao.UserDao;
import com.white.dao.impl.UserDaoImpl;
import com.white.pojo.User;
import org.junit.Test;

public class UserDaoTest {
    UserDao userDao = new UserDaoImpl();
    @Test
    public void queruserByUsername() {

        if(userDao.queruserByUsername("admin") == null){
            System.out.println("用户名可用");
        }
        else{
            System.out.println(userDao.queruserByUsername("admin"));
            System.out.println("用户名已存在");
        }
    }

    @Test
    public void queruserByUsernameAndPassword() {
        if (userDao.queruserByUsernameAndPassword("admin","123456")==null){
            System.out.println("用户名或密码错误,登录失败");
        }else{
            System.out.println("登录成功");
        }
    }

    @Test
    public void saveUser() {
        System.out.println(userDao.saveUser(new User(null,"admin1","123456","18992210106")));//插入数据库数据成功
    }
}

主键记得设置递增
1.RegistServlet程序接受注册请求
2.检测验证码是否正确
html
不加name无法传递参数

本作品采用 知识共享署名-相同方式共享 4.0 国际许可协议 进行许可