JDBC连接常用数据库基本的CURD-阿里云开发者社区

JDBC连接常用数据库基本的CURD

2017-07-04 1348

版权

本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。

本文涉及的产品

云数据库 RDS SQL Server，独享型 2核4GB

云数据库 RDS MySQL Serverless，0.5-2RCU 50GB

简介：

+关注继续查看

　JDBC连接各个数据库的className与url

JDBC连接DB2
private String className="com.ibm.db2.jdbc.net.DB2Driver";
private String url="jdbc:db2://localhost:8080/lwc";
JDBC连接Microsoft SQLServer(microsoft)
private String className="com.microsoft.jdbc.sqlserver.SQLServerDriver";
private String url="jdbc:microsoft:sqlserver://
localhost:1433;SelectMethod=Cursor;dataBaseName=lwc";
JDBC连接Sybase(jconn2.jar)
private String className="com.sybase.jdbc2.jdbc.SybDriver";
private String url="jdbc:sybase:Tds:localhost:2638";
JDBC连接MySQL(mm.mysql-3.0.2-bin.jar)
private String className="org.gjt.mm.mysql.Driver";
private String url="jdbc:mysql://localhost:3306/lwc";
JDBC连接PostgreSQL(pgjdbc2.jar)
private String className="org.postgresql.Driver";
private String url="jdbc:postgresql://localhost/lwc";
JDBC连接Oracle(classes12.jar)
private String className="oracle.jdbc.driver.OracleDriver";
private String url="jdbc:oracle:thin:@localhost:1521:lwc";

　　JDBC连接数据库案例

package com.itlwc;

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

public class DBConnection {
private static Connection conn = null;
private String user = "";
private String password = "";
private String className = "com.microsoft.jdbc.sqlserver.SQLServerDriver";
private String url = "jdbc:microsoft:sqlserver://"
+ "localhost:1433;SelectMethod=Cursor;dataBaseName=lwc";

private DBConnection() {
  try {
   Class.forName(this.className);
   conn = DriverManager.getConnection(url, user, password);
   System.out.println("连接数据库成功");
  } catch (ClassNotFoundException e) {
   System.out.println("连接数据库失败");
  } catch (SQLException e) {
   System.out.println("连接数据库失败");
  }
}

public static Connection getConn() {
  if (conn == null) {
   conn = (Connection) new DBConnection();
  }
  return conn;
}

// 关闭数据库
public static void close(ResultSet rs, Statement state, Connection conn) {
  if (rs != null) {
   try {
    rs.close();
   } catch (SQLException e) {
    e.printStackTrace();
   }
   rs = null;
  }
  if (state != null) {
   try {
    state.close();
   } catch (SQLException e) {
    e.printStackTrace();
   }
   state = null;
  }
  if (conn != null) {
   try {
    conn.close();
   } catch (SQLException e) {
    e.printStackTrace();
   }
   conn = null;
  }
}

// 测试数据库连接是否成功
public static void main(String[] args) {
getConn();
}
}

　基本CURD

获取数据库连接请查考JDBC连接常用数据库
private Connection conn = DBConnection.getConn();

　　增加方法

使用拼sql增加
public void add1(Student student) {
String sql = "insert into student values(" + student.getId() + ",'"
   + student.getCode() + "','" + student.getName() + "',"
   + student.getSex() + "," + student.getAge() + ")";
PreparedStatement ps = null;
try {
  ps = conn.prepareStatement(sql);
  ps.executeUpdate();
} catch (SQLException e) {
  e.printStackTrace();
}
DBConnection.close(null, ps, conn);
}
使用替换变量增加
public void add2(Student student) {
String sql = "insert into student values(?,?,?,?,?)";
PreparedStatement ps = null;
try {
  ps = conn.prepareStatement(sql);
  ps.setString(1, student.getCode());
  ps.setString(2, student.getName());
  ps.setString(3, student.getSex());
  ps.setString(4, student.getAge());
  ps.setString(5, student.getId());
  ps.executeUpdate();
} catch (SQLException e) {
  e.printStackTrace();
}
DBConnection.close(null, ps, conn);
}

　　删除方法

使用拼sql删除
public void delete1(String id) {
String sql = "delete from student where id='" + id+"'";
PreparedStatement ps = null;
try {
  ps = conn.prepareStatement(sql);
  ps.executeUpdate();
} catch (SQLException e) {
  e.printStackTrace();
}
DBConnection.close(null, ps, conn);
}
使用替换变量删除
public void delete2(String id) {
String sql = "delete from student where id=?";
PreparedStatement ps = null;
try {
  ps = conn.prepareStatement(sql);
  ps.setString(1, id);
  ps.executeUpdate();
} catch (SQLException e) {
  e.printStackTrace();
}
DBConnection.close(null, ps, conn);
}

　修改方法

使用拼sql修改
public void update1(Student student) {
String sql = "update student set code='" + student.getCode()
   + "',name='" + student.getName() + "',sex=" + student.getSex()
   + ",age=" + student.getAge() + " where id=" + student.getId();
PreparedStatement ps = null;
try {
  ps = conn.prepareStatement(sql);
  ps.executeUpdate();
} catch (SQLException e) {
  e.printStackTrace();
}
DBConnection.close(null, ps, conn);
}
使用替换变量修改
public void update2(Student student) {
String sql = "update student set code=?,name=?,sex=?,age=? where id=?";
PreparedStatement ps = null;
try {
  ps = conn.prepareStatement(sql);
  ps.setString(1, student.getCode());
  ps.setString(2, student.getName());
  ps.setString(3, student.getSex());
  ps.setString(4, student.getAge());
  ps.setString(5, student.getId());
  ps.executeUpdate();
} catch (SQLException e) {
  e.printStackTrace();
}
DBConnection.close(null, ps, conn);
}

　　查询方法

查询得到一个对象
public Student findById(int id) {
String sql = "select * from student where id=" + id;
Student student = new Student();
PreparedStatement ps = null;
ResultSet rs = null;
try {
  ps = conn.prepareStatement(sql);
  rs = ps.executeQuery();
  if (rs.next()) {
   student.setId(rs.getString(1));
   student.setCode(rs.getString(2));
   student.setName(rs.getString(3));
   student.setSex(rs.getString(4));
   student.setAge(rs.getString(5));
  }
} catch (SQLException e) {
  e.printStackTrace();
}
DBConnection.close(rs, ps, conn);
return student;
}
查询得到一组数据
@SuppressWarnings("unchecked")
public List find() {
String sql = "select * from student";
List list = new ArrayList();
PreparedStatement ps = null;
ResultSet rs = null;
try {
  ps = conn.prepareStatement(sql);
  rs = ps.executeQuery();
  while (rs.next()) {
   Student student = new Student();
   student.setId(rs.getString(1));
   student.setCode(rs.getString(2));
   student.setName(rs.getString(3));
   student.setSex(rs.getString(4));
   student.setAge(rs.getString(5));
   list.add(student);
  }
} catch (SQLException e) {
  e.printStackTrace();
}
DBConnection.close(rs, ps, conn);
return list;
}

　统计数据库总条数

public int getRows() {
int totalRows = 0;
String sql = "select count(*) as totalRows from student";
PreparedStatement ps = null;
ResultSet rs = null;
try {
  ps = conn.prepareStatement(sql);
  rs = ps.executeQuery();
  if (rs.next()) {
   totalRows = Integer.valueOf(rs.getString("totalRows"));
  }
} catch (SQLException e) {
  e.printStackTrace();
}
DBConnection.close(rs, ps, conn);
return totalRows;
}

　　执行存储过程

第一种
public String retrieveId(String tableName,String interval) throws SQLException {
     Connection conn = DBConnection.getConn();
     String sql = "exec p_xt_idbuilder '" + tableName + "','" + interval+ "'";
     PreparedStatement ps = conn.prepareStatement(sql);
  ResultSet rs = ps.executeQuery();
     String maxId = "";
     if(rs.next()){
         maxId = rs.getString("bh");
     }
     DBConnection.close(rs, ps, conn);
     return maxId;
}
第二种
public String retrieveId(String tableName,String interval) throws SQLException {
  Connection conn = DBConnection.getConn();
  CallableStatement cs = conn.prepareCall("{call p_xt_idbuilder(?,?,?)}");
     cs.setString(1, tableName);
     cs.setString(2, interval);
     cs.registerOutParameter(3,java.sql.Types.VARCHAR);
     cs.executeUpdate();
     String maxId = "";
     maxId=cs.getString(3);
  DBConnection.close(null, cs, conn);
  return maxId;
}

最新内容请见作者的GitHub页：http://qaseven.github.io/

JDBC连接常用数据库 基本的CURD

JDBC连接常用数据库基本的CURD