06

USE u ;
CREATE TABLE us(
id INT(20) NOT NULL,
NAME VARCHAR(20) NOT NULL,
sex VARCHAR(20) NOT NULL,
age INT(20) NOT NULL
);
SELECT * FROM us;
INSERT INTO us(id,NAME,sex,age ) VALUES (1,'zs','n',13);

package dao;
 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
 
 
public class DBConn {
    private static final String URL = "jdbc:mysql://localhost:3306/u";
    private static final String USERNAME = "root";
    private static final String PWD = "root";

    // 获取数据库的连接
    public static Connection getCon() throws Exception {
        // a.导入驱动，加载具体的驱动类
        Class.forName("com.mysql.jdbc.Driver");// 加载具体的驱动类
        // b.与数据库建立连接
        Connection con = DriverManager.getConnection(URL, USERNAME, PWD);
        return con;
    }

    public static void realse(ResultSet rs,Statement stmt,Connection con) {
        if(rs!=null) {
            try {
                rs.close();
            }catch(Exception e) {
                
                e.printStackTrace();
            }
            rs=null;
        }    
        if(stmt!=null) {
            try {
                stmt.close();
            }catch(Exception e) {
                
                e.printStackTrace();
            }
            stmt=null;
        }
        if(con!=null) {
            try {
                con.close();
            }catch(Exception e) {
                
                e.printStackTrace();
            }
            con=null;
        }
        
        
    }
}


    

package dao;

public class DeleteUserTest {
public static void main(String[] args) {
     UserD dao = new UserD();
     boolean flag =dao.delete(1);
     System.out.println(flag);
}
}

package dao;

import java.util.List;

public class FindAllUserTest {
    public static void main(String[] args){
         UserD dao = new UserD();
    List<UserDao> list = dao.findAllUser();
        System.out.println(list.size());
         
     }
}

package dao;

public class FindUserByIdTest {
public static void main(String[] args) {
     UserD dao = new UserD();
     UserDao u = dao.findUserById(1);
     System.out.println(u.getName());
}
}

package dao;


import dao.UserDao;

public class JdbcInsertTest {
    public static void main(String[] args){
     UserD dao = new UserD();
     UserDao us = new UserDao();
     us.setId(2);
     us.setName("zs");
     us.setSex("n");
     us.setAge(20);
     dao.insert(us);
     
 }
}

package dao;

public class UpdateUserTest {
    public static void main(String[] args) {
    UserD dao = new UserD();
     UserDao u = new UserDao();    
     u.setId(1);
     u.setName("ls");
     u.setAge(30);
     boolean flag = dao.update(u);
     System.out.println(flag);
    }
     
     
}

package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;



public class UserD {
//添加
    public boolean insert(UserDao us) {
        Connection con = null;
        Statement stmt = null;
        try {
            
            //获取连接对象
            con=DBConn.getCon();
            //获取执行sql语句对象
            stmt = con.createStatement();
            //执行sql
            String sql ="insert into us(id,name,sex,age)"+"values('"
                        +us.getId()+"','"
                        +us.getName()+"','"
                        +us.getSex()+"','"
                        +us.getAge()+"'"
                        +")";
            
            int row =stmt.executeUpdate(sql);
            if(row>0) {
                
                return true;
                
                
            }
        }catch(Exception e) {
             throw new RuntimeException(e);
        }finally {
            DBConn.realse(null, stmt, con);
        }
        
        return false;    
    }
    //查询全部
    public List<UserDao> findAllUser(){
        Connection con = null;
        Statement stmt = null;
        ResultSet rs =null;
        try {
            //获取连接对象
            con=DBConn.getCon();
            //获取执行sql语句对象
            stmt = con.createStatement();
            //执行sql
            String sql="select * from us";
            rs = stmt.executeQuery(sql);
            List<UserDao> list = new ArrayList<UserDao>();
            while(rs.next()) {
                UserDao us = new UserDao();
                us.setId(rs.getInt("id"));
                us.setName(rs.getString("name"));
                us.setSex(rs.getString("sex"));
                us.setAge(rs.getInt("age"));
                list.add(us);
            }
            return list;
            
        }catch(Exception e) {
             throw new RuntimeException(e);
        }finally {
            DBConn.realse(rs, stmt, con);
        }
        
    }
    //根据id查询
    public UserDao findUserById(int id){
        Connection con = null;
        PreparedStatement stmt = null;
        ResultSet rs =null;
        try {
            //获取连接对象
            con=DBConn.getCon();
            //获取执行sql语句对象
            String sql="select * from us where id=?";
            
            stmt = con.prepareStatement(sql);
            //执行sql
            stmt.setInt(1, id);
            rs = stmt.executeQuery();
            
            if(rs.next()) {
                UserDao us = new UserDao();
                us.setId(rs.getInt("id"));
                us.setName(rs.getString("name"));
                us.setSex(rs.getString("sex"));
                us.setAge(rs.getInt("age"));
             return us;
            }

            
        }catch(Exception e) {
             throw new RuntimeException(e);
        }finally {
            DBConn.realse(rs, stmt, con);
        }
        return null;
        
    }
    //修改
    public boolean update(UserDao us) {
        Connection con = null;
         PreparedStatement stmt = null;
        try {
            
            //获取连接对象
            con=DBConn.getCon();
            //获取执行sql语句对象
            String sql ="update us set name = ?,age = ? where id = ?";
            stmt = con.prepareStatement(sql);
            //执行sql
           
            stmt.setString(1, us.getName());
            stmt.setInt(2, us.getAge());
             stmt.setInt(3, us.getId());
            int row =stmt.executeUpdate();
            if(row>0) {
                
                return true;
                
                
            }
        }catch(Exception e) {
             throw new RuntimeException(e);
        }finally {
            DBConn.realse(null, stmt, con);
        }
        
        return false;    
        
        
    }
    //删除
    public boolean delete(int id) {
        
        Connection con = null;
         PreparedStatement stmt = null;
        try {
            
            //获取连接对象
            con=DBConn.getCon();
            //获取执行sql语句对象
            String sql ="delete from us where id = ?";
            stmt = con.prepareStatement(sql);
            //执行sql
    
             stmt.setInt(1, id);
            int row =stmt.executeUpdate();
            if(row>0) {
                
                return true;
                
                
            }
        }catch(Exception e) {
             throw new RuntimeException(e);
        }finally {
            DBConn.realse(null, stmt, con);
        }
        
        return false;    
        
        
    }
    
    
    
    
}

package dao;

public class UserDao {
    private int id;
    private String name;
    private String sex;
    private int age;

    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 getSex() {
    return sex;
    }
    public void setSex(String sex) {
    this.sex = sex;
    }
    public int getAge() {
    return age;
    }
    public void setAge(int age) {
    this.age = age;
    }

}