jdbc连接数据库方案（2）

jdbc连接数据库方案（2）

• 数据库连接工具类可以创建连接和释放资源：
  
  package com.jiaofenbin.Utils;
  
  import java.sql.*;
  
  public class Jdbcutil {
      private  static final String DRIVER_CLASS="com.mysql.jdbc.Driver";
      private  static  final String URL="jdbc:mysql://127.0.0.1:3306/test";
      private  static  final String USER_NAME="root";
      private  static  final String PASSWORD="123";
  
      public  static  Connection getConn() throws Exception {
              Class.forName(DRIVER_CLASS);
              return DriverManager.getConnection(URL,USER_NAME,PASSWORD);
      }
      public  static void close(ResultSet res,PreparedStatement ps,Connection  conn)  throws  Exception{
              if(res!=null) {
                  res.close();
              }
              if(ps!=null) {
                  ps.close();
              }
              if(conn!=null) {
                  conn.close();
              }
      }
  
  
  }
  

•   实现类：

            

package com.jiaofengbin.entity.dao;

import com.jiaofenbin.Utils.Jdbcutil;
import org.testng.annotations.Test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.HashMap;
import java.util.Map;


public class UserDao {
    private  Connection  conn =null;
    private  PreparedStatement   ps=null;
    private  ResultSet res=null;

    
    public void addUser() throws Exception {
        try {
            String sql = "insert into user VALUES(null ,'西西','女','学习高手');";
            //获取数据库连接
              conn = Jdbcutil.getConn();
              ps = conn.prepareStatement(sql);
             int  result = ps.executeUpdate();
//            System.out.println(result);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            Jdbcutil.close(res, ps, conn);
        }


    }

    
    public void deleteUserByName(String name) throws Exception {
        try {
            String sql = "delete from user where username=?;";
            //获取数据库连接
            conn = Jdbcutil.getConn();
            ps = conn.prepareStatement(sql);
            ps.setString(1, name);
            int res = ps.executeUpdate();
            System.out.println(res);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            Jdbcutil.close(res, ps, conn);

        }
    }
        
        public Map getUserByName (String name) throws Exception {
            Map map = new HashMap();
            try {
                String sql = "SELECT * from user  where username=? ";
                //获取数据库连接
               conn = Jdbcutil.getConn();
               ps = conn.prepareStatement(sql);
                ps.setString(1, name);
                res = ps.executeQuery();
                if (res.next()) {
                    map.put("id", res.getInt("id"));
                    map.put("username", res.getString("username"));
                    map.put("sex", res.getString("sex"));
                    map.put("stills", res.getString("stills"));
                }
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                Jdbcutil.close(res, ps, conn);
            }
            return map;
        }

    }

•    测试类：  

package com.jiaofengbin.entity.demo;

import com.jiaofengbin.entity.dao.UserDao;
import org.testng.Assert;
import org.testng.annotations.Test;

import java.util.Map;


public class TestDemo {
    @Test(priority = 1)
    public static void testAdd() throws Exception {
        UserDao userDao=new UserDao();
        userDao.addUser();
    }

    @Test(priority = 2)
    public  void testSelect() throws Exception {
     UserDao   userDao=new UserDao();
     Map map=userDao.getUserByName("西西");
       // System.out.println(map);
        Assert.assertEquals("学习高手",map.get("stills"));
        Assert.assertEquals("女",map.get("sex"));
    }

    @Test(priority = 3)
    public  void testDelete() throws Exception {
        UserDao   userDao=new UserDao();
        userDao.deleteUserByName("彤彤");

    }
}