Idea创建maven项目--java工程连接mysql数据库

1. 准备表和数据

   -- ----------------------------
   -- Table structure for user
   -- ----------------------------
   DROP TABLE IF EXISTS `user`;
   CREATE TABLE `user`  (
     `id` int(11) NOT NULL AUTO_INCREMENT,
     `username` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
     `password` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
     PRIMARY KEY (`id`) USING BTREE
   ) ENGINE = InnoDB AUTO_INCREMENT = 9 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
   
   -- ----------------------------
   -- Records of user
   -- ----------------------------
   INSERT INTO `user` VALUES (1, 'root', '1234');
   INSERT INTO `user` VALUES (2, 'xiaoming', 'qqqq');
   INSERT INTO `user` VALUES (4, '刘港澳', '1111');
   INSERT INTO `user` VALUES (5, '六花', '勇太');
   INSERT INTO `user` VALUES (6, '黑', 'silver');
   INSERT INTO `user` VALUES (7, '碳治郎', '弥豆子');
   INSERT INTO `user` VALUES (8, 'Nick', '1234');
   

2. 打开idea，新建项目

   新建项目
   
   不使用骨架创建java工程，直接next
   
   填写项目必须信息
   

3. 等待项目创建完后，通过pom.xml添加依赖

   <dependencies>
       <dependency>
           <groupId>mysql</groupId>
           <artifactId>mysql-connector-java</artifactId>
           <version>5.1.47</version>
       </dependency>
       <dependency>
           <groupId>junit</groupId>
           <artifactId>junit</artifactId>
           <version>4.12</version>
           <scope>test</scope>
       </dependency>
   </dependencies>
   

4. 按照图新建包结构
   

5. 分别新建User类，IUserDao接口，UserDaoImpl实现类，UserTest测试类

   User类

   package com.cncs.domain;
   
   public class User {
       private int id;
       private String username;
       private String password;
   
       @Override
       public String toString() {
           return "User{" +
                   "id=" + id +
                   ", username='" + username + '\'' +
                   ", password='" + password + '\'' +
                   '}';
       }
   
       public int getId() {
           return id;
       }
   
       public void setId(int 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;
       }
   }
   

   IUserDao接口

   package com.cncs.dao;
   
   import com.cncs.domain.User;
   import java.util.List;
   
   public interface IUserDao {
   
       List<User> findAll() throws Exception;
   }
   

   UserDaoImpl实现类

   package com.cncs.dao.impl;
   
   import com.cncs.dao.IUserDao;
   import com.cncs.domain.User;
   
   import java.sql.*;
   import java.util.ArrayList;
   import java.util.List;
   
   public class UserDaoImpl implements IUserDao {
       public List<User> findAll() throws Exception {
           //加载驱动类
           Class.forName("com.mysql.jdbc.Driver");
           //获取连接
           Connection conn = DriverManager.getConnection("jdbc:mysql:///test","root","123456");
           //获取预处理对象
           PreparedStatement ps = conn.prepareCall("select * from user");
           //执行sql
           ResultSet rs = ps.executeQuery();
           //封装user
           List<User> users = new ArrayList<User>();
           while(rs.next()){
               User user = new User();
               user.setId(rs.getInt("id"));
               user.setUsername(rs.getString("username"));
               user.setPassword(rs.getString("password"));
               users.add(user);
           }
           //释放资源
           rs.close();
           ps.close();
           conn.close();
           return users;
       }
   }
   

   UserTest测试类

   package com.cncs.test;
   
   import com.cncs.dao.IUserDao;
   import com.cncs.dao.impl.UserDaoImpl;
   import com.cncs.domain.User;
   import org.junit.Test;
   
   import java.util.List;
   
   public class UserTest {
   
       @Test
       public  void findAll() throws Exception {
           IUserDao userdao = new UserDaoImpl();
           List<User> users = userdao.findAll();
           System.out.println(users);
       }
   }
   

6. 进行junit单元测试，测试结果如下