jdbc连接数据库代码详解

1.DBUtil.java

package com.pcb.dbutil;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class Dbutil {
    //数据库URL和账号密码
        private static final String connectionURL="jdbc:mysql://localhost:3306/pcb2?useUnicode=true&characterEncoding=GB18030&useSSL=false&serverTimezone=GMT&allowPublicKeyRetrieval=true";
        private static final String username="root";
        private static final String password="123456";
        
        //数据库连接
        public static Connection getConnection()
        {
            try {
                Class.forName("com.mysql.cj.jdbc.Driver");
               // System.out.print("数据库连接成功");
                return DriverManager.getConnection(connectionURL,username,password);
            } catch (Exception e) {
                // TODO: handle exception
                System.out.println("数据库连接失败");
                e.printStackTrace();
                return null;
            }
        }
        public static void closeAll(Connection connection,PreparedStatement statement,ResultSet rSet)
        {
            try {
                if(connection!=null)
                    connection.close();
            } catch (SQLException e) {
                // TODO 自动生成的 catch 块
                e.printStackTrace();
            }
        
            try {
                if(statement!=null)
                    statement.close();
            } catch (SQLException e) {
                // TODO 自动生成的 catch 块
                e.printStackTrace();
            }
        
            try {
                if(rSet!=null)
                    rSet.close();
            } catch (SQLException e) {
                // TODO 自动生成的 catch 块
                e.printStackTrace();
            }
        }
            
        //关闭connection和preparedstatement
        public static void closePart(Connection connection,PreparedStatement statement)
        {
            try {
                if(connection!=null)
                    connection.close();
            } catch (SQLException e) {
                // TODO 自动生成的 catch 块
                e.printStackTrace();
            }
        
            try {
                if(statement!=null)
                    statement.close();
            } catch (SQLException e) {
                // TODO 自动生成的 catch 块
                e.printStackTrace();
            }
        }
        
        public static void main(String[] args) {
            getConnection();
        }
}

2.数据库的增删改查操作

   1.查

   

//获得个人信息
       public static ArrayList<userbeen> getuerinfor(String username){
             Connection con=null;
             PreparedStatement pstmt=null;
             ResultSet rs=null;       
           try {
               con=dbutil.getConnection();//连接数据库
               String sql="select *from user where username='"+username+"'";
               System.out.print(sql);
               pstmt=con.prepareStatement(sql);
               rs=pstmt.executeQuery();
               ArrayList<userbeen> list=new ArrayList<userbeen>();
               while (rs.next()) {
                   userbeen user=new userbeen();
                   user.setId(rs.getString(1));
                   user.setUsername(rs.getString(2));
                   user.setPassword(rs.getString(3));
                   user.setName(rs.getString(4));
                   user.setSex(rs.getString(5));
                   user.setBirthday(rs.getString(6));
                   user.setEmail(rs.getString(7));
                   user.setPhone(rs.getString(8));
                   user.setAddress(rs.getString(9));
                   
                   list.add(user);
                
            }
               return list;
           }
               catch (SQLException e) {
//                System.out.println("");
                e.printStackTrace();
            }
            finally {
                dbutil.closeAll(con, pstmt, rs);
            }
         
       
       return null;
       }

2.增加数据

//用户注册
       public static String adduser(userbeen user) {
          Connection con=null;
          PreparedStatement pstmt=null;
          ResultSet rs=null;       
          try {
                //连接数据库
                con=dbutil.getConnection();
                //查重
                String sql0="select * from user where username='"+user.getUsername()+"'";
                pstmt=con.prepareStatement(sql0);
                rs=pstmt.executeQuery();
                if (rs.next()) {
                    return "username";
                }
                String sql_email="select * from user where email=?";
                pstmt=con.prepareStatement(sql_email);
                pstmt.setString(1, user.getEmail());
                rs=pstmt.executeQuery();
                if(rs.next()) {
                    return "email";
                }
                String sql_phone="select * from user where phone=?";
                pstmt=con.prepareStatement(sql_email);
                pstmt.setString(1, user.getPhone());
                rs=pstmt.executeQuery();
                if(rs.next()) {
                    return "phone";
                }
                //添加到用户表
                String sql="insert into user(username,password,name,sex,birthday,email,phone,address,type) values(?,?,?,?,?,?,?,?,?)";
                System.out.println(user.getUsername());
                pstmt=con.prepareStatement(sql);
                pstmt.setString(1, user.getUsername());
                pstmt.setString(2, user.getPassword());
                pstmt.setString(3, user.getName());
                pstmt.setString(4, user.getSex());
                pstmt.setString(5, user.getBirthday());
                pstmt.setString(6, user.getEmail());
                pstmt.setString(7, user.getPhone());
                pstmt.setString(8, user.getAddress());
                pstmt.setString(9, "2");
                pstmt.executeUpdate();
                
                //添加到登录表
                String sql2="insert into enter(username,password,type) value(?,?,?)";
                pstmt=con.prepareStatement(sql2);
                pstmt.setString(1, user.getUsername());
                pstmt.setString(2, user.getPassword());
                pstmt.setString(3, "2");
                pstmt.executeUpdate();
                return "yes";
                        
            }
            catch (SQLException e) {
                System.out.println("用户添加失败");
                e.printStackTrace();
            }
            finally {
                dbutil.closeAll(con, pstmt, rs);
            }
         
           return "no";
           
       }

3.修改数据

   //更新个人信息
       public static boolean updateuserinfor(userbeen user,String ord) {
           Connection con=null;
           PreparedStatement pstmt=null;
           ResultSet rs=null;
           try {
               
             con=dbutil.getConnection();
             //获取用户id
             String sql0="select id from user where username='"+ord+"'";  
//             System.out.print(sql0);
             pstmt=con.prepareStatement(sql0);
             rs=pstmt.executeQuery();
             String id = null;
             while(rs.next()){
                 id=rs.getString(1);
//                 System.out.print(id);
             }
            //   
            
            String sql="update user set username=?,name=?,sex=?,birthday=?,email=?,phone=?,address=? where id='"+id+"'";
            pstmt=con.prepareStatement(sql);
//            System.out.print(sql);
            pstmt.setString(1, user.getUsername());
            pstmt.setString(2, user.getName());
            pstmt.setString(3, user.getSex());
            pstmt.setString(4, user.getBirthday());
            pstmt.setString(5, user.getEmail());
            pstmt.setString(6, user.getPhone());
            pstmt.setString(7, user.getAddress());
            
            pstmt.executeUpdate();
            
            
            //更新登录表；
            String sql2="update enter set username=? where username='"+ord+"'";
            pstmt=con.prepareStatement(sql2);
            pstmt.setString(1, user.getUsername());
            pstmt.executeUpdate();            
            
            return true;
            
            
        } catch (Exception e) {
            // TODO: handle exception
        }
           
          

4.删除数据

delete from 表名 where id =?