Jdbc数据库CRUD简单实例

JDBC实现操作MsSql数据库实例

项目目录结构:

src
   com
      demo
          model
                  Student.java
                  
   database
           DBConnection.java
           User.java
           UserDAO.java
           
WebContent
       action.jsp
       index.jsp
       submit.jsp
           
    WEB-INF
        lib
                sqljdbc4.jar

 

数据库结构:

create table t_user(
    [id]  [int] not NULL primary key,
    [name]  [varchar](50) not NULL
)

模型Student.java

package com.demo.model;

public class Student {
    public String Name;
}

数据库访问:

DBConnection.java

package database;
import java.sql.*;
import java.sql.Statement;

public class DBConnection {
    private static final String URL = "jdbc:sqlserver://127.0.0.1:1433;databaseName=Sample;";

    private static final String USERNAME = "sa";

    private static final String PASSWORD = "******";

    private static final String DRIVER = "com.microsoft.sqlserver.jdbc.SQLServerDriver";

    static {
        try {
            Class.forName(DRIVER);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(URL, USERNAME, PASSWORD);
    }

    public static void close(Connection connection, Statement statement, ResultSet resultSet) {
        try {
            if(connection != null) {
                connection.close();
            }
            if(statement != null) {
                statement.close();
            }
            if(resultSet != null) {
                resultSet.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void close(Connection connection, Statement statement) {
        close(connection, statement, null);
    }
}

User.java

package database;

public class User {
    private int id;

    private String name;

    public User(int id, String name) {
        this.id = id;
        this.name = name;
    }

    public User(String name) {
        this.name = name;
    }

    public void setId(int id) {
        this.id = id;
    }

    public int getId() {
        return this.id;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getName() {
        return this.name;
    }

    @Override
    public String toString() {
        return "#" + id + " name: " + name;
    }
}
View Code

UserDAO.java

package database;
import java.sql.*;
import java.util.ArrayList;
import java.util.LinkedList;
import java.util.List;

public class UserDAO {
    private Connection connection;
    /*
    private Connection getConnection() throws SQLException {
        String URL = "jdbc:sqlserver://127.0.0.1:1433;databaseName=Sample;";
        String USERNAME = "sa";
        String PASSWORD = "******";
        String DRIVER = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
        Connection con = null; 
        try {
            Class.forName(DRIVER);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        con = DriverManager.getConnection(URL,USERNAME,PASSWORD);
        return con;
    }
    */
    public UserDAO() throws SQLException {
        //this.connection = getConnection();
        this.connection = DBConnection.getConnection();
        try {
            connection.setAutoCommit(true);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    public UserDAO(Connection connection) {
        this.connection = connection;
        try {
            connection.setAutoCommit(true);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public void addUser(String id,String name) {
        String sql = "INSERT INTO t_user(id,name) VALUES (?,?)";
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, id);
            preparedStatement.setString(2, name);
            preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBConnection.close(connection, preparedStatement);
        }
    }

    public void removeUser(String name) {
        String sql = "DELETE FROM t_user WHERE name = ?";
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, name);
            preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBConnection.close(connection, preparedStatement);
        }
    }

    public String getNameById(int id) {
        String sql = "SELECT name FROM t_user WHERE id = ?";
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        String name = null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, id);
            resultSet = preparedStatement.executeQuery();
            resultSet.next();
            name = resultSet.getString(1);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBConnection.close(connection, preparedStatement, resultSet);
        }
        return name;
    }

    public void updateUser(int id, String name) {
        String sql = "UPDATE t_user set name = ? where id = ?";
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, name);
            preparedStatement.setInt(2, id);
            preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBConnection.close(connection, preparedStatement);
        }
    }

    public List<User> getAllUsers() {
        String sql = "SELECT * FROM t_user";
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        List<User> userList = null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
            userList = new LinkedList<User>();
            while(resultSet.next()) {
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                userList.add(new User(id, name));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBConnection.close(connection, preparedStatement, resultSet);
        }
        List<User> returnedList = new ArrayList<User>();
        returnedList.addAll(userList);
        return returnedList;
    }
}
View Code

 

WebContent页面

index.jsp

 1 <%@ page language="java" contentType="text/html; charset=ISO-8859-1" pageEncoding="ISO-8859-1"%>
 2 <%@ page import="database.*" %>
 3 <%@ page import="java.util.List" %>
 4 <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
 5 <html>
 6 <head>
 7 <meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
 8 <title>Insert title here</title>
 9 </head>
10 <body>
11 <%
12 List<User> userList=new UserDAO().getAllUsers();
13 for(int i = 0;i < userList.size(); i ++){
14   System.out.print(userList.get(i));
15 }
16 %>
17 </body>
18 </html>

submit.jsp

 1 <%@ page language="java" contentType="text/html; charset=ISO-8859-1"
 2     pageEncoding="ISO-8859-1"%>
 3 <%@ page import="database.*" %>
 4 <%@ page import="java.util.List" %>
 5 <%
 6 
 7 String path = request.getContextPath(); 
 8 String basePath =  request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/"; 
 9 
10 %>
11 <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
12 <html>
13 <head>
14 <base href="<%=basePath%>">
15 <meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
16 <title>Insert title here</title>
17 </head>
18 <body>
19 <center> 
20 <h2>input form</h2><hr> 
21 <form action="action.jsp?action=insert" method="post" id="form"> 
22 
23 <h4>id:<input type="text" name="id" ></input><br></h4> 
24 
25 <h4>name:<input type="text" name="name"></input><br></h4> 
26 
27 <input type="submit" value="submit"/> 
28 
29 </form> 
30 
31 <div>
32 <%
33 List<User> userList=new UserDAO().getAllUsers();
34 for(int i = 0;i < userList.size(); i ++){
35   out.print(userList.get(i).getId());
36   out.print(userList.get(i).getName());
37 }
38 %>
39 </div>
40 
41 </center>  
42 </body>
43 </html>

action.jsp

 1 <%@ page language="java" contentType="text/html; charset=ISO-8859-1"
 2     pageEncoding="ISO-8859-1"%>
 3 <%@ page import="database.*" %>
 4 <%
 5 
 6 String path = request.getContextPath(); 
 7 String basePath =  request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/"; 
 8 
 9 %>
10 <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
11 <html>
12 <head>
13 <meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
14 <title>Insert title here</title>
15 </head>
16 <body>
17 <%
18 request.setCharacterEncoding("UTF-8"); 
19 String action=request.getParameter("action"); 
20 if(action.equals("delete"))
21 {
22     String name=request.getParameter("name"); 
23     new UserDAO().removeUser(name);
24     System.out.println(name);
25 }else if(action.equals("insert")){
26     String id=request.getParameter("id"); 
27     String name=request.getParameter("name"); 
28     new UserDAO().addUser(id, name);
29     System.out.println(name);
30 }else{
31     System.out.println(action);
32 }
33 %>
34 </body>
35 </html>

 

posted @ 2016-04-28 14:00  undefined?  阅读(314)  评论(0)  编辑  收藏  举报