论坛徽章:: 0

电梯直达

1楼 [收藏(0)] [报告]

发表于 2009-07-02 13:04 |只看该作者 |倒序浏览

下面是连接数据库的代码，sqlserver2000需要打上sp3的补丁，jdbc sp2的驱动注意版本对应

package demo;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class ConnectionManager {
    private static String username = "sa";
    private static String password = "secret";
    private static String dUrl = "jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=demo";
    private static String dDriver = "com.microsoft.jdbc.sqlserver.SQLServerDriver";
    private static Connection conn = null;

    public static Connection getConnection() {
        try{
            Class.forName(dDriver);
        }catch(ClassNotFoundException e){
            e.printStackTrace();
        }
        try{
            conn=DriverManager.getConnection(dUrl,username,password);
        }catch(Exception e){
            e.printStackTrace();
        }
        return conn;
    }

    public void rollback() {
        try {
            conn.rollback();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public void commit() {
        try {
            conn.commit();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void close(ResultSet rs, PreparedStatement pstmt,
            Connection conn) {
        try {
            if (rs != null) {
                rs.close();
            }
            if (pstmt != null) {
                pstmt.close();
            }
            if (conn != null) {
                conn.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

[ 本帖最后由 freelogin 于 2009-7-2 13:10 编辑 ]

文库|博客

freelogin

白手起家

论坛徽章:: 0

2楼 [报告]

发表于 2009-07-02 13:07 |只看该作者

下面是实体类代码

package demo;

public class Employee {
    private int e_id;
    private String name;
    private String password;
    private String email;
    
    public int getE_id() {
        return e_id;
    }
    public void setE_id(int e_id) {
        this.e_id = e_id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public String getPassword() {
        return password;
    }
    public void setPassword(String password) {
        this.password = password;
    }
    public String getEmail() {
        return email;
    }
    public void setEmail(String email) {
        this.email = email;
    }
}

下面是数据库访问部分

package demo;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class EmployeeManager {

    public Employee createEmployee(Employee emp) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        String insertSql = "insert into employee(name,password,email) values(?,?,?)";
        try {
            conn = ConnectionManager.getConnection();
            pstmt = conn.prepareStatement(insertSql);
            pstmt.setString(1, emp.getName());
            pstmt.setString(2, emp.getPassword());
            pstmt.setString(3, emp.getEmail());
            pstmt.executeUpdate();
        } catch (SQLException e) {
        }
        return emp;
    }

    public Employee updateEmployee(Employee emp) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        String insertSql = "update employee set name=? , password=? , email=? where e_id = ?";
        try {
            conn = ConnectionManager.getConnection();
            pstmt = conn.prepareStatement(insertSql);
            pstmt.setString(1, emp.getName());
            pstmt.setString(2, emp.getPassword());
            pstmt.setString(3, emp.getEmail());
            pstmt.setInt(4, emp.getE_id());
            pstmt.executeUpdate();
        } catch (SQLException e) {
        }
        return emp;
    }

    public Employee getEmployee(int id) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        Employee emp = null;
        ResultSet rs = null;
        String insertSql = "select * from employee where e_id = ?";
        try {
            conn = ConnectionManager.getConnection();
            pstmt = conn.prepareStatement(insertSql);
            pstmt.setInt(1, id);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                emp = new Employee();
                emp.setE_id(rs.getInt("e_id"));
                emp.setName(rs.getString("name"));
                emp.setPassword(rs.getString("password"));
                emp.setEmail(rs.getString("email"));
            }
        } catch (SQLException e) {
        }
        return emp;
    }

    public void removeEmployee(int id) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        String insertSql = "delete from employee where e_id = ?";
        try {
            conn = ConnectionManager.getConnection();
            pstmt = conn.prepareStatement(insertSql);
            pstmt.setInt(1, id);
            pstmt.executeUpdate();
        } catch (SQLException e) {
        }
    }

}

实战分享：从技术角度谈机器学习入门| 【大话IT】RadonDB低门槛向MySQL集群下战书 | ChinaUnix打赏功能已上线！ | 新一代分布式关系型数据库RadonDB知多少？

freelogin

白手起家

论坛徽章:: 0

3楼 [报告]

发表于 2009-07-02 13:09 |只看该作者

这是测试下可以运行

package demo;

public class EmployeeTest {
    public static void add(){
        Employee emp = new Employee();
        EmployeeManager em = new EmployeeManager();
        emp.setName("jack");
        emp.setPassword("secret");
        emp.setEmail("god@gmail.com");
        em.createEmployee(emp);        
    }
    
    public static void update(int id){
        Employee emp = new Employee();
        EmployeeManager em = new EmployeeManager();
        emp.setName("tom");
        emp.setPassword("helem");
        emp.setEmail("jack@sina.com");
        emp.setE_id(id);
        em.updateEmployee(emp);
    }
    
    public static void printEmployee (){
        Employee emp = new Employee();
        EmployeeManager em = new EmployeeManager();
        emp=em.getEmployee(2);
        System.out.println(emp.getE_id());
        System.out.println(emp.getName());
        System.out.println(emp.getPassword());
        System.out.println(emp.getEmail());        
    }
    
    public static void del(int id){        
        EmployeeManager em = new EmployeeManager();
        em.removeEmployee(id);        
    }
    
    public static void main(String[] args){
        //add();

        //update(2);

        //printEmployee();

        //del(1);

        //System.out.println("---");

    }
}

实战分享：从技术角度谈机器学习入门| 【大话IT】RadonDB低门槛向MySQL集群下战书 | ChinaUnix打赏功能已上线！ | 新一代分布式关系型数据库RadonDB知多少？

freelogin

白手起家

论坛徽章:: 0

4楼 [报告]

发表于 2009-07-02 17:16 |只看该作者

加上SQL 语句吧

create database demo;
use demo
create table employee(e_id identity primary key,name varchar(30),password varchar(20),email varchar(20));

实战分享：从技术角度谈机器学习入门| 【大话IT】RadonDB低门槛向MySQL集群下战书 | ChinaUnix打赏功能已上线！ | 新一代分布式关系型数据库RadonDB知多少？

freelogin

白手起家

论坛徽章:: 0

5楼 [报告]

发表于 2009-07-02 17:19 |只看该作者

刚刚少了获得全部员工信息的方法
如下

public List<Employee> getAllEmployees() {
        Connection conn = null;
        PreparedStatement pstmt = null;
        Employee emp = null;
        ResultSet rs = null;
        List<Employee> list = new ArrayList<Employee>();        
        String querySql = "select * from employee";
        try {
            conn = ConnectionManager.getConnection();
            pstmt = conn.prepareStatement(querySql);            
            rs = pstmt.executeQuery();
            while (rs.next()) {
                emp = new Employee();
                emp.setE_id(rs.getInt("e_id"));
                emp.setName(rs.getString("name"));
                emp.setPassword(rs.getString("password"));
                emp.setEmail(rs.getString("email"));
                list.add(emp);
            }
        } catch (SQLException e) {
        }
        return list;
    }

下面是测试方法

List<Employee> list =em.getAllEmployees();
          for(int i=0;i<list.size();i++){
              emp=(Employee)list.get(i);
              System.out.println(emp.getE_id());
              System.out.println(emp.getName());
              System.out.println(emp.getPassword());
              System.out.println(emp.getEmail());
          }