一、工具类
public final class JdbcUtils {
private static String url="jdbc:mysql://localhost:3306/jdbc";
private static String user="root";
private static String password="******";
private JdbcUtils(){
}
static{
try {
// 注册驱动
Class.forName("com.mysql.jdbc.Driver");//推荐
// DriverManager.registerDriver(new com.mysql.jdbc.Driver());//2
// System.setProperty("jdbc.drivers", "com.mysql.jdbc.Driver[:...:...]");//注册方式3可以注册多个驱动用“:”分隔。
} catch (ClassNotFoundException e) {
throw new ExceptionInInitializerError(e);
}
}
public static Connection getConnection() throws SQLException{
return DriverManager.getConnection(url, user, password);
}
public static void free(ResultSet rs,Statement st,Connection conn){
try {
if(rs!=null)
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}finally{
try {
if(st!=null)
st.close();
} catch (SQLException e) {
e.printStackTrace();
}finally{
try {
if(conn!=null)
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
二、应用类
public class Base {
public static void main(String[] args) {
template();
}
static void template(){
Connection conn=null;
Statement st=null;
ResultSet rs=null;
try {
// 建立连接
conn=JdbcUtils.getConnection();
// 创建语句
st=conn.createStatement();
// 执行语句
rs=st.executeQuery("select * from user");
// 处理结果
while(rs.next()){
System.out.println(rs.getObject(1)+"\t"+rs.getObject(2)
+"\t"+rs.getObject(3)+"\t"+rs.getObject(4));
}
} catch (Exception e) {
e.printStackTrace();
}finally{
// 释放资源
JdbcUtils.free(rs, st, conn);
}
}
}
三、几个API
* PreparedStatement.getGeneratedKeys()
* PreparedStatement ps=connection.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
* ps.executeUpdate();
* ResultSet rs=st.getGeneratedKeys();
* rs.getInt(1);
可用于判断是否操作成功。
public class OtherApi {
public static void main(String[] args) {
int id=create();
System.out.println("i="+id);
}
static int create(){
int id=0;
Connection conn=null;
PreparedStatement ps=null;
ResultSet rs=null;
try {
// 建立连接
// conn=JdbcUtils.getConnection();
conn=JdbcUtilsSing.getInstance().getConnection();//使用单例
String sql="insert into user(name,birthday,money)values('name1 gk','1987-01-01',400)";
ps=conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);//插入之后从JDBC驱动将主键返回给程序
ps.executeUpdate();
rs=ps.getGeneratedKeys();//返回rs是因为主键可能不同的类型,也可能是复合主键 。
if(rs.next()){
id=rs.getInt(1);
}
System.out.println("i="+id);
} catch (Exception e) {
e.printStackTrace();
}finally{
// 释放资源
JdbcUtils.free(rs, ps, conn);
}
return id;
}
}
---------------------------------------------------------------------------------------------------------------
* 批处理,可以幅提升大量增、删、改的速度。
* PreparedStatement.addBatch();
* PreparedStatement.executeBatch();
public class BatchTest {
public static void main(String[] args) {
long start=System.currentTimeMillis();
/*for(int i=0;i<1000;i++){
create(i);
}*/
createBatch();
long end=System.currentTimeMillis();
System.out.println("create:"+(end-start));
}
/*static void create(int i){
int id=0;
Connection conn=null;
PreparedStatement ps=null;
ResultSet rs=null;
try {
// 建立连接
// conn=JdbcUtils.getConnection();
conn=JdbcUtilsSing.getInstance().getConnection();//使用单例
String sql="insert into user(name,birthday,money)values(?,?,?)";
ps=conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);//插入之后从JDBC驱动将主键返回给程序
ps.setString(1, "batch name"+i);
ps.setDate(2, new Date(System.currentTimeMillis()));
ps.setFloat(3, 100f+i);
ps.executeUpdate();
rs=ps.getGeneratedKeys();//返回rs是因为主键可能不同的类型,也可能是复合主键 。
if(rs.next()){
id=rs.getInt(1);
}
System.out.println("i="+id);
} catch (Exception e) {
e.printStackTrace();
}finally{
// 释放资源
JdbcUtils.free(rs, ps, conn);
}
// return id;
}*/
static void createBatch(){
Connection conn=null;
PreparedStatement ps=null;
ResultSet rs=null;
try {
// 建立连接
// conn=JdbcUtils.getConnection();
conn=JdbcUtilsSing.getInstance().getConnection();//使用单例
String sql="insert into user(name,birthday,money)values(?,?,?)";
ps=conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);//插入之后从JDBC驱动将主键返回给程序
for(int i=0;i<1000;i++){
ps.setString(1, "batch name"+i);
ps.setDate(2, new Date(System.currentTimeMillis()));
ps.setFloat(3, 100f+i);
ps.addBatch();
}
int[] is=ps.executeBatch();
} catch (Exception e) {
e.printStackTrace();
}finally{
// 释放资源
JdbcUtils.free(rs, ps, conn);
}
}
}
-----------------------------------------------------------------------------------------------------------------------------------
* 可滚动的结果集
* Statement st=connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATEBLE);
* ResultSet rs=st.executeQuery(sql);
* rs.beforeFirst();rs.afterLast();rs.first();rs.isFirst();rs.last();rs.isLast();rs.absolute(9);rs.moveToInsertRow();
public class ScrollTest {
public static void main(String[] args) {
scroll();
}
static void scroll() {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
// 建立连接
// conn=JdbcUtils.getConnection();
conn = JdbcUtilsSing.getInstance().getConnection();// 使用单例
String sql = "select id,name,birthday,money from user";
ps = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_READ_ONLY);
rs = ps.executeQuery();
// rs.afterLast();//到最后一行的后一行,此时要读到记录必须前移
// rs.beforeFirst();//到第一行的前面
// boolean b=rs.first();//判断当前是不是第一行
// boolean b=rs.last();//判断当前是不是最后一行
while (rs.next()) {
// System.out.println(rs.getObject(1)+"\t"+rs.getObject(2)+"\t"+rs.getObject(3)+"\t"+rs.getObject(4));
System.out.println(rs.getInt("id") + "\t"
+ rs.getString("name") + "\t" + rs.getDate("birthday")
+ "\t" + rs.getFloat("money"));
}
System.out.println("---------------------------------------");
rs.absolute(5);//定位到第五行,可用于分页,要全部查出再分页,效率低
int i=0;
while(rs.previous()&&i++<3) {
System.out.println(rs.getInt("id") + "\t"
+ rs.getString("name") + "\t" + rs.getDate("birthday")
+ "\t" + rs.getFloat("money"));
}
} catch (Exception e) {
e.printStackTrace();
} finally {
// 释放资源
JdbcUtils.free(rs, ps, conn);
}
}
}
------------------------------------------------------------------------------------------------------------
* 可更新的结果集
* conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATEBLE);
* rs.updateString("col name","new value");
* rs.updateRow();
static void read(){
Connection conn=null;
PreparedStatement ps=null;
ResultSet rs=null;
try {
// 建立连接
// conn=JdbcUtils.getConnection();
conn=JdbcUtilsSing.getInstance().getConnection();//使用单例
String sql="select id,name,birthday,money from user where id>7";
ps=conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
rs=ps.executeQuery();
while(rs.next()){
// System.out.println(rs.getObject(1)+"\t"+rs.getObject(2)+"\t"+rs.getObject(3)+"\t"+rs.getObject(4));
System.out.println(rs.getInt("id")+"\t"
+rs.getString("name")+"\t"
+rs.getDate("birthday")+"\t"
+rs.getFloat("money"));
String name=rs.getString("name");
if("batch name2".equals(name)){
rs.updateFloat("money", 200f);
rs.updateRow();
}
}
} catch (Exception e) {
e.printStackTrace();
}finally{
// 释放资源
JdbcUtils.free(rs, ps, conn);
}
}
四、数据库源信息
public class DBMD {
public static void main(String[] args) throws SQLException {
Connection conn=JdbcUtils.getConnection();
DatabaseMetaData dbmd=conn.getMetaData();//返回数据库的源信息
System.out.println("db name:"+dbmd.getDatabaseProductName());//数据库名
System.out.println("tx:"+dbmd.supportsTransactions());//是否支持事务
conn.close();
}
}
----------------------------------------------------------------------------------------------------------------
public class ParameterMetaTest {
public static void main(String[] args) {
Object[] params = new Object[] { "lisi",new Date(System.currentTimeMillis()), 100f };
read("select * from user where name=? and birthday<? and money>?",params);
}
static void read(String sql, Object[] params) {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
// 建立连接
// conn=JdbcUtils.getConnection();
conn = JdbcUtilsSing.getInstance().getConnection();// 使用单例
ps = conn.prepareStatement(sql);
// ParameterMetaData pmd = ps.getParameterMetaData();
// int count = pmd.getParameterCount();
// for (int i = 1; i <= count; i++) {
// ps.setObject(i, params[i - 1]);
// }
for (int i = 1; i <= params.length; i++) {
ps.setObject(i, params[i - 1]);
}
// for (int i = 1; i <= count; i++) {
// System.out.print(pmd.getParameterClassName(i)+"\t");
// System.out.print(pmd.getParameterType(i)+"\t");
// System.out.println(pmd.getParameterTypeName(i));
// }
rs = ps.executeQuery();
while (rs.next()) {
System.out.println(rs.getInt("id") + "\t" + rs.getString("name") + "\t" + rs.getDate("birthday")+ "\t" + rs.getFloat("money"));
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JdbcUtils.free(rs, ps, conn);
}
}
}
----------------------------------------------------------------------------------------------------------------------
ResultSetMetaData meta = rs.getMetaData();
public class ResultSetMetaDataTest {
public static void main(String[] args) {
List<Map<String,Object>> datas=read("select * from user where id<5");
System.out.println(datas);
}
static List<Map<String, Object>> read(String sql) {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
List<Map<String,Object>> datas=null;
Map<String,Object> data=null;
try {
// 建立连接
// conn=JdbcUtils.getConnection();
conn = JdbcUtilsSing.getInstance().getConnection();// 使用单例
ps = conn.prepareStatement(sql);
rs = ps.executeQuery();
ResultSetMetaData rsmd = rs.getMetaData();
int count = rsmd.getColumnCount();
String[] colNames=new String[count];
for (int i = 1; i <= count; i++) {
// System.out.print(rsmd.getColumnClassName(i) + "\t");
// System.out.print(rsmd.getColumnName(i) + "\t");
// System.out.println(rsmd.getColumnLabel(i) + "\t");
colNames[i-1]=rsmd.getColumnLabel(i);//如果设置了别名使用,没有的话Name与Label一样
}
datas=new ArrayList<Map<String,Object>>();
while (rs.next()) {
data=new HashMap<String,Object>();
for(int i=0;i<colNames.length;i++){
data.put(colNames[i], rs.getObject(colNames[i]));
}
datas.add(data);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JdbcUtils.free(rs, ps, conn);
}
return datas;
}
}