完整的JDBC 简单列子
package dao;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class DBConnection {
private static final String DBDRIVER = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
private static final String DBURL = "jdbc:sqlserver://localhost:1433;databaseName=study";
private static final String DBUSER = "sa";//用户名
private static final String PASSWORD = "woai5jia";//密码
static {
try {
Class.forName(DBDRIVER);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
public static Connection getConnection(){
try {
return DriverManager.getConnection(DBURL,DBUSER,PASSWORD);
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
}
package dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import entity.User;
public class UserDAO {
/*查看表的相关信息*/
public void findColumnInfo(){
Connection con = DBConnection.getConnection();
PreparedStatement ps =null;
String sql = "select * from userinfo";
ResultSet rs ;
try {
ps = con.prepareStatement(sql);
rs = ps.executeQuery();
ResultSetMetaData rd = rs.getMetaData();
int len = rd.getColumnCount();
for(int i=1;i<=len;i++){
System.out.print("列名"+rd.getColumnName(i)+" ");
System.out.println("类型"+rd.getColumnTypeName(i));
}
rs.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/*查找一个表中的多行记录*/
public List<User> findUsers(int startIndex,int number){
if(number<=0){
return null;
}
Connection con = DBConnection.getConnection();
PreparedStatement ps = null;
String sql = "select * from userinfo";
try {
ps = con.prepareStatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
ResultSet rs = ps.executeQuery();
if (! rs.absolute(startIndex)) {
return null;
}
List<User> users = new ArrayList<User>(number);
do{
User user = new User();
user.setId(rs.getInt("id"));
user.setName(rs.getString("name"));
user.setPassword(rs.getString("password"));
users.add(user);
}while(rs.next() && --number>0);
rs.last();
System.out.println(rs.getRow());//计算总行数
rs.close();//最后要把流关闭
return users;
}catch (SQLException e) {
e.printStackTrace();
} finally {
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return null;
}
/*通过组号来查找用户*/
public static List<User> findUsersByGno(int gno){
Connection con = DBConnection.getConnection();
String sql = "select * from userinfo where gno=?";
PreparedStatement ps = null;
try {
ps = con.prepareStatement(sql);
ps.setInt(1, gno);
ResultSet rs = ps.executeQuery();
boolean result = rs.next();
if (!result) {
return null;
}
List<User> users = new ArrayList<User>();
do{
User user = new User();
user.setId(rs.getInt("id"));
user.setName(rs.getString("name"));
user.setPassword(rs.getString("password"));
user.setGno(rs.getInt("gno"));
users.add(user);
}while(rs.next() && users.size()>0)