Java-JDBC_简单示例CURD

例1.查询--全部查询


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




public class PrepareStatmentSelect {
private static final String sqldriverName = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
private static final String sqldbURL = "jdbc:sqlserver://localhost:1433;TestDB"; // database换成你的数据库名称
private static final String sqluse = "username"; // username换成你的SQLServer登录名
private static final String sqlpwd = "password"; // password换成你的SQLServer登陆密码


public static void main(String[] args) {
try {
// step1: 加载驱动程序，此时不需要实例化，由容器自己负责管理
Class.forName(sqldriverName);


// step2:连接数据库
Connection conn = DriverManager.getConnection(sqldbURL, sqluse, sqlpwd);


// step3: 进行数据库数据操作
String keyword="李";
String sql_select = "select AAC001,AAC002,AAC003,AAC006 from AC01_00 ";
PreparedStatement stmt = conn.prepareStatement(sql_select);
ResultSet rs = stmt.executeQuery();
while(rs.next()){
String aac001 = rs.getString(1);
String aac002 = rs.getString(2);
String aac003 = rs.getString(3);
Date aac006 = rs.getDate(4);
System.out.println("社保号："+aac001+"身份证号："+aac002+"姓名："+aac003+"出生日期"+aac006);
}


// step4: 关闭连接
rs.close();
stmt.close();
conn.close();
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}
}
}




例2：查询--模糊
String sql_select_like = "select AAC001,AAC002,AAC003,AAC006 from AC01_00 where AAC003 LIKE ?  " ;
PreparedStatement stmt = conn.prepareStatement(sql_select_like);
String keyword="李";
stmt.setString(1, "%"+ keyword + "%");


ResultSet rs = stmt.executeQuery();
while(rs.next()){
String aac001 = rs.getString(1);
String aac002 = rs.getString(2);
String aac003 = rs.getString(3);
Date aac006 = rs.getDate(4);
System.out.println("社保号："+aac001+"身份证号："+aac002+"姓名："+aac003+"出生日期"+aac006);
}




例3：查询--分页
int currentPage = 1;
int lineSize = 5;
String sql_select_page = " select * from ( "
+ " select AAC001,AAC002,AAC003,AAC006, ROWNUM rn "
+ " from AC01_00 where AAC003 LIKE ?  and ROWNUM <= ? ) temp "
+ " where temp.rn>? " ;
PreparedStatement stmt = conn.prepareStatement(sql_select_page);
String keyword="李";
stmt.setString(1, "%"+ keyword + "%");
stmt.setInt(1, currentPage * lineSize );
stmt.setInt(2, (currentPage - 1) * lineSize);
ResultSet rs = stmt.executeQuery();
while(rs.next()){
String aac001 = rs.getString(1);
String aac002 = rs.getString(2);
String aac003 = rs.getString(3);
Date aac006 = rs.getDate(4);
System.out.println("社保号："+aac001+"身份证号："+aac002+"姓名："+aac003+"出生日期"+aac006);
}


例4：查询--统计
String sql_select_count = " select count(aac001) from  AC01_00 where AAC003 LIKE ?  " ;
PreparedStatement stmt = conn.prepareStatement(sql_select_count);
String keyword="李";
stmt.setString(1, "%"+ keyword + "%");


ResultSet rs = stmt.executeQuery();
if(rs.next()){
int count = rs.getInt(1);
System.out.println("查询行数为："+ count);
}