此方法不使用实体类,因为对数据库操作完返回的是Map数组存储并返回前台
连接数据库:
private final static String DRIVER = "com.mysql.jdbc.Driver";
private final static String URI = "jdbc:mysql://localhost:3306/mydb?useUnicod=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai";
private final static String USERNAME = "root";
private final static String PASSWORD = "root";
private static ResultSet rs = null;
private static PreparedStatement ps = null;
private static Connection conn = null;
private static void init() {
try {
Class.forName(DRIVER);
conn = DriverManager.getConnection(URI, USERNAME, PASSWORD);
} catch (ClassNotFoundException e) {
e.printStackTrace();
System.out.println("未找到数据库驱动");
} catch (SQLException e) {
e.printStackTrace();
System.out.println("未获取数据库连接");
}
}
查询单条数据:
/**
* 查询单条
*
* @param sql
* @return
*/
public static Map selectOne(String sql, Object[] obj) {
List<String> fields = getFields(sql.split("where")[0]);
HashMap map = new HashMap();
init();
try {
ps = conn.prepareStatement(sql);
System.out.println(sql);
for (int i = 1; i <= obj.length; i++) {
System.out.println(obj[i-1]);
ps.setObject(i, obj[i - 1]);
}
rs = ps.executeQuery();
while (rs.next()) {
for (String field : fields) {
map.put(field, rs.getObject(field));
}
}
} catch (SQLException e) {
System.out.println("sql执行错误");
}finally {
close();
}
return map;
}
查询全部数据:
/**
* 查询
*
* @param sql
* @return
*/
public static List<Map> selectALL(String sql, Object[] obj) {
List<String> fields = getFields(sql.split("where")[0]);
List<Map> list = new ArrayList<Map>();
init();
try {
ps = conn.prepareStatement(sql);
System.out.println(sql);
for (int i = 1; i <= obj.length; i++) {
System.out.println(obj[i-1]);
ps.setObject(i, obj[i - 1]);
}
rs = ps.executeQuery();
while (rs.next()) {
HashMap map = new HashMap();
for (String field : fields) {
map.put(field, rs.getObject(field));
}
list.add(map);
}
} catch (SQLException e) {
System.out.println("sql执行错误");
}finally {
close();
}
return list;
}
对数据库增删改操作:
/**
* 更新
*
* @param sql
* @return
*/
public static int update(String sql, Object[] obj) {
init();
try {
ps = conn.prepareStatement(sql);
System.out.println(sql);
for (int i = 1; i <= obj.length; i++) {
System.out.println(obj[i-1]);
ps.setObject(i, obj[i - 1]);
}
return ps.executeUpdate();
} catch (SQLException e) {
System.out.println("sql执行错误");
}finally {
close();
}
return 0;
}
获取数据行数:
/**
* 功能描述:获取条数
* @param sql:
* @param obj:
* @return: int
**/
public static int getCount(String sql, Object[] obj) {
init();
int count = 0;
try {
ps = conn.prepareStatement(sql);
for (int i = 1; i <= obj.length; i++) {
ps.setObject(i, obj[i - 1]);
}
rs = ps.executeQuery();
while (rs.next()) {
count = rs.getInt(1);
}
} catch (SQLException e) {
System.out.println("sql执行错误");
}finally {
close();
}
return count;
}
关闭流:
public static void close() {
try {
if (rs != null) {
rs.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
try {
if (ps != null) {
ps.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
try {
if (conn != null) {
conn.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
综合方法:
private final static String DRIVER = "com.mysql.jdbc.Driver";
private final static String URI = "jdbc:mysql://localhost:3306/mydb?useUnicod=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai";
private final static String USERNAME = "root";
private final static String PASSWORD = "root";
private static ResultSet rs = null;
private static PreparedStatement ps = null;
private static Connection conn = null;
private static void init() {
try {
Class.forName(DRIVER);
conn = DriverManager.getConnection(URI, USERNAME, PASSWORD);
} catch (ClassNotFoundException e) {
e.printStackTrace();
System.out.println("未找到数据库驱动");
} catch (SQLException e) {
e.printStackTrace();
System.out.println("未获取数据库连接");
}
}
public static void main(String[] args) {
init();
System.out.println(conn);
}
private static List<String> getFields(String sql) {
List<String> list = new ArrayList<>();
init();
try {
ps = conn.prepareStatement(sql);
rs = ps.executeQuery();
ResultSetMetaData rmd = rs.getMetaData();
// 获取列的个数
int num = rmd.getColumnCount();
for (int i = 1; i <= num; i++) {
// 根据下标获取的表的字段名
list.add(rmd.getColumnName(i));
}
} catch (SQLException e) {
System.out.println("预编译失败");
}finally {
close();
}
return list;
}
/**
* 查询
*
* @param sql
* @return
*/
public static List<Map> selectALL(String sql, Object[] obj) {
List<String> fields = getFields(sql.split("where")[0]);
List<Map> list = new ArrayList<Map>();
init();
try {
ps = conn.prepareStatement(sql);
System.out.println(sql);
for (int i = 1; i <= obj.length; i++) {
System.out.println(obj[i-1]);
ps.setObject(i, obj[i - 1]);
}
rs = ps.executeQuery();
while (rs.next()) {
HashMap map = new HashMap();
for (String field : fields) {
map.put(field, rs.getObject(field));
}
list.add(map);
}
} catch (SQLException e) {
System.out.println("sql执行错误");
}finally {
close();
}
return list;
}
/**
* 查询单条
*
* @param sql
* @return
*/
public static Map selectOne(String sql, Object[] obj) {
List<String> fields = getFields(sql.split("where")[0]);
HashMap map = new HashMap();
init();
try {
ps = conn.prepareStatement(sql);
System.out.println(sql);
for (int i = 1; i <= obj.length; i++) {
System.out.println(obj[i-1]);
ps.setObject(i, obj[i - 1]);
}
rs = ps.executeQuery();
while (rs.next()) {
for (String field : fields) {
map.put(field, rs.getObject(field));
}
}
} catch (SQLException e) {
System.out.println("sql执行错误");
}finally {
close();
}
return map;
}
/**
* 更新
*
* @param sql
* @return
*/
public static int update(String sql, Object[] obj) {
init();
try {
ps = conn.prepareStatement(sql);
System.out.println(sql);
for (int i = 1; i <= obj.length; i++) {
System.out.println(obj[i-1]);
ps.setObject(i, obj[i - 1]);
}
return ps.executeUpdate();
} catch (SQLException e) {
System.out.println("sql执行错误");
}finally {
close();
}
return 0;
}
public static void close() {
try {
if (rs != null) {
rs.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
try {
if (ps != null) {
ps.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
try {
if (conn != null) {
conn.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
/**
* 功能描述:获取条数
* @param sql:
* @param obj:
* @return: int
**/
public static int getCount(String sql, Object[] obj) {
init();
int count = 0;
try {
ps = conn.prepareStatement(sql);
for (int i = 1; i <= obj.length; i++) {
ps.setObject(i, obj[i - 1]);
}
rs = ps.executeQuery();
while (rs.next()) {
count = rs.getInt(1);
}
} catch (SQLException e) {
System.out.println("sql执行错误");
}finally {
close();
}
return count;
}