Java JDBC基本操作-优快云博客

本文介绍了使用Java连接SQL Server数据库进行数据操作的方法，并通过实体类实现数据的增删改查。具体包括获取数据库连接、执行SQL语句、关闭资源等步骤，以及演示了如何通过实体类与数据库表进行交互。

2019独角兽企业重金招聘Python工程师标准>>>

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

public class DBHelp {

// 1.下面是几个常量
private static final String DRIVER = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
private static final String URL = "jdbc:sqlserver://localhost:1433;DatabaseName=CentralWallet";
private static final String DB_NAME = "sa";
private static final String DB_PASSWORD = "system123";

/**
* 获取数据库的连接
*
* @return
*/

public Connection getConnection() {
Connection con = null;

  try {
   Class.forName(DRIVER);
   con = DriverManager.getConnection(URL, DB_NAME, DB_PASSWORD);
  } catch (ClassNotFoundException e) {
   e.printStackTrace();
  } catch (SQLException e) {
   e.printStackTrace();
  }
  return con;

}

/**
* 该方法用来执行insert,update,delete操作
*
* @param sql
* SQL语句
* @param args
* 不定项参数
* @return
*/

public int executeSQL(String sql, Object... args) {
  Connection con = getConnection();
  PreparedStatement sta = null;
  int rows = 0;

try {
sta = con.prepareStatement(sql);

   // ★注意下面的循环语句★
   for (int i = 0; i < args.length; i++) {
    sta.setObject(i + 1, args[i]); // 为什么是i+1呢？因为你从前面的文章知道，那是从1开始的！
   }

   rows = sta.executeUpdate();
   if (rows > 0) {
    System.out.println("operate successfully !");
   } else {
    System.out.println("fail!");
   }

  } catch (SQLException e) {
   e.printStackTrace();
  } finally {
   this.close(sta, con);
  }
  return rows;
}

/**
* rs,sta,con的关闭
*
* @param rs
* @param sta
* @param con
*/
public void close(ResultSet rs, PreparedStatement sta, Connection con) {
  try {
   if (rs != null) {
    rs.close();
   }
  } catch (SQLException e) {
   e.printStackTrace();
  } finally {
   try {
    if (sta != null) {
     sta.close();
    }
   } catch (SQLException e) {
    e.printStackTrace();
   } finally {
    try {
     if (con != null) {
      con.close();
     }
    } catch (SQLException e) {
     e.printStackTrace();
    }
   }
  }
}

/**
* sta,con的关闭方法
*
* @param sta
* @param con
*/
public void close(PreparedStatement sta, Connection con) {
this.close(null, sta, con);
}

}

//实体类。不多写了。。

public class BaseUsrAdmininfo {

    private String adminid;
    private String firstname;

//get set 省略

｝
//--------------------------------userDAo测试

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

/**
* 测试
*
* @author bear
*
*/
public class UserDao {
private DBHelp db = new DBHelp();

public DBHelp getDb() {
return db;
}

public void setDb(DBHelp db) {
this.db = db;
}

/**
* 对t_user的插入操作
*
* @param user
* @return
*/
public int insert(BaseUsrAdmininfo user) {
String sql = "insert base_Usr_AdminInfo(firstname,lastname) values(?,?)";
return db.executeSQL(sql, user.getFirstname(), user.getLastname());
}

/**
* 对t_user的更新操作
*
* @param user
* @return
*/
public int update(BaseUsrAdmininfo user) {
  String sql = "update base_Usr_AdminInfo set firstname=?,firstname=? where AdminId=?";
  return db.executeSQL(sql, user.getFirstname(), user.getLastname(),
    user.getAdminid());
}

/**
* 对t_user的删除操作
*
* @param id
* @return
*/
public int delete(int id) {
String sql = "delete from base_Usr_AdminInfo where AdminId=?";
return db.executeSQL(sql, id);
}

/**
* 对t_user的全检索
*
* @return
*/

public List<BaseUsrAdmininfo> retrieve() {
  List<BaseUsrAdmininfo> list = new ArrayList<BaseUsrAdmininfo>();
  Connection con = db.getConnection();
  PreparedStatement sta = null;
  ResultSet rs = null;
  try {
   String sql = "select * from base_Usr_AdminInfo";
   sta = con.prepareStatement(sql);
   rs = sta.executeQuery();
   while (rs.next()) {
    BaseUsrAdmininfo user = new BaseUsrAdmininfo();
    user.setAdminid(rs.getString("AdminId"));
    list.add(user);
   }

  } catch (SQLException e) {
   e.printStackTrace();
  } finally {
   db.close(rs, sta, con);
  }

return list;

}

/**
* 跟id进行检索
*
* @param id
*            用户的id号
* @return
*/
public BaseUsrAdmininfo retrieveById(int id) {
  Connection con = db.getConnection();
  PreparedStatement sta = null;
  ResultSet rs = null;
  BaseUsrAdmininfo user = null;

  try {
   String sql = "select firstname from base_Usr_AdminInfo AdminId =?";
   sta = con.prepareStatement(sql);
   sta.setInt(1, id);
   rs = sta.executeQuery();
   if (rs.next()) {
    user = new BaseUsrAdmininfo();
    user.setFirstname(rs.getString("firstname"));
   }

  } catch (SQLException e) {
   e.printStackTrace();
  } finally {
   db.close(rs, sta, con);
  }
  return user;
}

/**
* 测试
*/
public static void main(String[] args) {
  UserDao userDao = new UserDao();
  List<BaseUsrAdmininfo> list = userDao.retrieve();
  for (BaseUsrAdmininfo s : list) {
   System.out.println(s.getAdminid());
  }
}
}