package jdbc;
import domain.Server;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
/**
* Created by Solitude on 2017-6-2.
*/
public class ServerJdbc {
public static void main(String[] args){
//all();
//pageServer(1, 20);
//getTotalCount();
//searchServers("inIp", "4.1");
orderByServer("inIp", "DESC");
}
public static ArrayList<Server> getServers(ResultSet rs){
ArrayList<Server> servers = new ArrayList<>();
try {
while (rs.next()){
Server server = new Server();
server.setId(rs.getInt("id"));
server.setServerName(rs.getString("serverName"));
server.setInIp(rs.getString("inIp"));
server.setOutIp(rs.getString("outIp"));
server.setUsage(rs.getString("usage"));
server.setDepartment(rs.getString("department"));
server.setContact(rs.getString("contact"));
server.setComment(rs.getString("comment"));
servers.add(server);
System.out.println("server department : " + server.getInIp());
}
} catch (SQLException e) {
e.printStackTrace();
}
return servers;
}
//查找所有信息
public static ArrayList<Server> all() {
String sql = "SELECT * FROM `server`";
Connection conn = MyJDBC.getConn();
ArrayList<Server> servers =null;
try {
PreparedStatement ptst = conn.prepareStatement(sql);
ResultSet rs = ptst.executeQuery();
servers = getServers(rs);
} catch (SQLException e) {
e.printStackTrace();
}
return servers;
}
//查找单页信息
public static ArrayList<Server> pageServer(int pageNow, int pageSize) {
String sql = "SELECT * FROM `server` LIMIT ?, ?";
Connection conn = MyJDBC.getConn();
ArrayList<Server> servers = null;
try {
PreparedStatement ptst = conn.prepareStatement(sql);
ptst.setInt(1, (pageNow - 1) * pageSize);
ptst.setInt(2, pageSize);
ResultSet rs = ptst.executeQuery();
servers = ServerJdbc.getServers(rs);
}catch (SQLException e){
e.printStackTrace();
}
MyJDBC.close(conn);
return servers;
}
//获得总数量String sql = "SELECT count(*) FROM server";
public static int getTotalCount(){
String sql = "SELECT count(*) FROM server";
Connection conn = MyJDBC.getConn();
int totalCount = 0;
try {
PreparedStatement ptst = conn.prepareStatement(sql);
ResultSet rs = ptst.executeQuery(); //只有一行，存放总数
while (rs.next()){
totalCount = rs.getInt(1);
}
} catch (SQLException e) {
e.printStackTrace();
}
System.out.print(totalCount);
return totalCount;
}
/**
* 模糊查找给定字段
*/
public static ArrayList<Server> searchServers(String choice, String searchChar){
String sql = "select * from server where `" + choice + "` like '%" + searchChar + "%'";
System.out.println(sql);
Connection conn = MyJDBC.getConn();
ArrayList<Server> servers = null;
try {
PreparedStatement ptst = conn.prepareStatement(sql);
ResultSet rs = ptst.executeQuery();
servers = getServers(rs);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return servers;
}
/**
* 对某个字段进行升/降排序
* select from server order by inIp ASC; 升
* select from server order by inIp DESC; 降
*/
public static ArrayList<Server> orderByServer(String choice, String ascOrDesc){
String sql = "select * from `server` order by " + choice + " " + ascOrDesc;
//String sql = "SELECT * FROM `server` order by ? ?";
ArrayList<Server> servers = null;
System.out.println(choice + " " + ascOrDesc);
try {
Connection conn = MyJDBC.getConn();
PreparedStatement ptst = conn.prepareStatement(sql);
//ptst.setString(1, choice);
//ptst.setString(2, ascOrDesc);
ResultSet rs = ptst.executeQuery();
servers =getServers(rs);
} catch (SQLException e) {
e.printStackTrace();
}
return servers;
}
}
转载请注明原文地址: https://www.6miu.com/read-73817.html
