通过JDBC链接数据库并执行数据库语句

此表中的内容是JDBC链接数据库并用于调用sql语句

import java.sql.Connection;  
import java.sql.PreparedStatement;  
import java.sql.ResultSet;  
import java.sql.SQLException;  
import java.sql.Statement;  
import java.util.ArrayList;  
import java.util.HashMap;  
import java.util.List;  
import java.util.Map;  
  
import com.yy.servlet.po.User;  
import com.yy.servlet.util.DBconnectionUitl;  
  
/** 
 * @author kouyang 
 * 
 */  
public class UserDao {  
    //单例模式  
    public UserDao(){  
          
    }  
    private static UserDao userDao = new UserDao();  
    public static UserDao getInstance(){  
        return userDao;  
    }  
      
    public boolean saveUser(User user){  
        boolean flag = false;  
          
        Connection conn = null;  
        Statement st = null;  
        String sql = "";  
        try {  
            //获取连接  
            conn = DBconnectionUitl.getConnection();  
            //产生执行sql语句的对象  
            st = conn.createStatement();  
              
            //拼凑sql语句  
            sql = "insert into user(username,sex,password) values ('"   
                + user.getUsername() + "',"  
                + user.getSex() + ",'"  
                + user.getPassword() + "'"  
                + ")";  
            System.out.println(sql);  
            //最好使用PreparedStatement此处为示例  
            //执行sql语句  
            int rows = st.executeUpdate(sql);  
            if(rows != 0)  
                flag = true;  
        } catch (SQLException e) {  
            e.printStackTrace();  
        } finally {  
            DBconnectionUitl.closeAll(null, st, conn);  
        }  
        return flag;  
    }  
      
    public User getMap(Map<String,String> map){  
    //查询数据库中表user中的username与password  
        User user = null;  
          
        Connection conn = null;  
        PreparedStatement ps = null;  
          
        ResultSet rs = null;  
        String sql = "select * from user where username = ? and password = ?";//添加sql语句  
        try {  
            conn = DBconnectionUitl.getConnection();//加载驱动并链接数据库（封装内容）  
            ps = conn.prepareStatement(sql);//执行sql语句  
            ps.setString(1, map.get("username"));//sql语句中的第一个问号  
            ps.setString(2, map.get("password"));//sql语句中的第二个问号  
              
            rs = ps.executeQuery();  
            while(rs.next()){  
                user = new User();  
                user.setUserId(rs.getInt("userId"));  
                user.setUsername(rs.getString("username"));  
                user.setSex(rs.getInt("sex"));  
                user.setPassword(rs.getString("password"));  
                user.setFlag(rs.getInt("flag"));  
            }//通过while循环获取查询到的数据  
        } catch (SQLException e) {  
            e.printStackTrace();  
        } finally {  
            DBconnectionUitl.closeAll(rs, ps, conn);//关闭  
        }  
        return user;  
    }  
      
      
    public boolean saveUser2(User user2){  
    //通过jdbc把username与password添加到表user中  
        boolean flag = false;  
          
        Connection conn = null;  
        PreparedStatement ps  = null;  
        String sql = "insert into user(username,password) values (?,?)";  
        try {  
            conn = DBconnectionUitl.getConnection();  
            ps = conn.prepareStatement(sql);  
            ps.setString(1,user2.getUsername());  
            ps.setString(2,user2.getPassword());  
            System.out.println(sql);  
            int rows = ps.executeUpdate();  
            if(rows != 0)  
                flag = true;  
        } catch (SQLException e) {  
            e.printStackTrace();  
        } finally {  
            DBconnectionUitl.closeAll(null, ps, conn);  
        }  
        return flag;  
  
    }  
      
      
    public User getUserByusername(String username){  
//通过表user中的username查询表user中的相关信息  
        User user = null;  
          
        Connection conn = null;  
        PreparedStatement ps = null;  
          
        ResultSet rs = null;  
        String sql = "select * from user where username = ?";  
        try {  
            conn = DBconnectionUitl.getConnection();  
            ps = conn.prepareStatement(sql);  
            ps.setString(1, username);  
              
            rs = ps.executeQuery();  
            while(rs.next()){  
                user = new User();  
                user.setUserId(rs.getInt("userId"));  
                user.setUsername(rs.getString("username"));  
                user.setSex(rs.getInt("sex"));  
                user.setPassword(rs.getString("password"));  
                user.setFlag(rs.getInt("flag"));  
            }  
        } catch (SQLException e) {  
            e.printStackTrace();  
        } finally {  
            DBconnectionUitl.closeAll(rs, ps, conn);  
        }  
        return user;  
    }

此段代码为上段的封装内容

import java.io.InputStream;  
import java.sql.Connection;  
import java.sql.DriverManager;  
import java.sql.PreparedStatement;  
import java.sql.ResultSet;  
import java.sql.SQLException;  
import java.sql.Statement;  
import java.util.Properties;  
  
//封装jdbc中的关联数据库与关闭界面  
public class DBconnectionUitl {  
    private static String jdbcDriver = "";  
    private static String jdbcURL = "";  
    private static String jdbcUser = "";  
    private static String jdbcPassword = "";  
      
      
    static {  
        InputStream is = null;//获取数据流  
        Properties p = new Properties();  
        try {  
            is = DBconnectionUitl.class.getClassLoader().getResourceAsStream("jdbc.properties");//通过数据流调用jdbc.properties中存储的登录信息  
            p.load(is);  
              
            jdbcDriver = p.getProperty("jdbcDriver");  
            jdbcURL = p.getProperty("jdbcURL");  
            jdbcUser = p.getProperty("jdbcUser");  
            jdbcPassword = p.getProperty("jdbcPassword");  
            //匹配jdbc.properties中的相关信息  
        } catch (IOException e) {  
            e.printStackTrace();  
        }  
    }  
      
    public static Connection getConnection(){  
        Connection conn = null;  
        try {  
            Class.forName(jdbcDriver);//加载驱动  
            conn = DriverManager.getConnection(jdbcURL, jdbcUser, jdbcPassword);//链接数据库  
        } catch (ClassNotFoundException e) {  
            e.printStackTrace();  
        } catch (SQLException e) {  
            e.printStackTrace();  
        }  
        return conn;  
    }  
      
    public static void closeAll(ResultSet rs,Statement st,Connection conn){  
    //关闭时奉行先运行后关闭后运行先关闭的原则  
        try {  
            if(rs != null)  
                rs.close();//关闭ResultSet  
        } catch (Exception e) {  
            e.printStackTrace();  
        } finally {  
            try {  
                if(st != null)  
                    st.close();//关闭Statement  
            } catch (Exception e) {  
                e.printStackTrace();  
            } finally {  
                try {  
                    if(conn != null)  
                        conn.close();//关闭Connection  
                } catch (Exception e) {  
                    e.printStackTrace();  
                }  
            }  
        }  
    }  
      
    public static void closeAll(ResultSet rs,PreparedStatement ps,Connection conn){  
        try {  
            if(rs != null)  
                rs.close();  
        } catch (Exception e) {  
            e.printStackTrace();  
        } finally {  
            try {  
                if(ps != null)  
                    ps.close();  
            } catch (Exception e) {  
                e.printStackTrace();  
            } finally {  
                try {  
                    if(conn != null)  
                        conn.close();  
                } catch (Exception e) {  
                    e.printStackTrace();  
                }  
            }  
        }  





用于存储并表user的相关信息

public class User {  
    private int userId;  
    private String username;  
    private int sex;  
    private String password;  
    private int flag;  
      
    public int getUserId() {  
        return userId;  
    }  
    public void setUserId(int userId) {  
        this.userId = userId;  
    }  
    public String getUsername() {  
        return username;  
    }  
    public void setUsername(String username) {  
        this.username = username;  
    }  
    public int getSex() {  
        return sex;  
    }  
    public void setSex(int sex) {  
        this.sex = sex;  
    }  
    public String getPassword() {  
        return password;  
    }  
    public void setPassword(String password) {  
        this.password = password;  
    }  
    public int getFlag() {  
        return flag;  
    }  
    public void setFlag(int flag) {  
        this.flag = flag;  
    }  





附带文件jdbc.properties中的内容

表user

下接Servlet关联数据库后验证登录信息和注册