package util;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
public class DBUtil {
private static String url="jdbc:mysql://localhost:3306/hospital?characterEncoding=utf8";
// &serverTimezone=UTC
private static final String user="root";
private static final String password="12345";
private static Connection conn=null;
private static PreparedStatement ps=null;
private static ResultSet rs=null;
static {
try {
Class.forName("com.mysql.jdbc.Driver");
//init();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
private static void init(){
String sql="create database if not exists hospital";
executeUpdate(sql,null);
url="jdbc:mysql://localhost:3306/hospital?characterEncoding=utf8&serverTimezone=UTC";
}
public static Connection createConn(){
try {
return DriverManager.getConnection(url,user,password);
System.out.println(user+","password);
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
public static ResultSet executeQuery(String sql, Object[] params){
conn=createConn();
System.out.println("连接成功");
try {
ps=conn.prepareStatement(sql);
prepare(ps,params);
rs=ps.executeQuery();
System.out.println("查询成功");
} catch (SQLException e) {
e.printStackTrace();
}
return rs;
}
public static boolean executeUpdate(String sql, Object[] params){
conn=createConn();
int i=0;
try {
ps=conn.prepareStatement(sql);
prepare(ps,params);
i=ps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}finally {
closeAll();
}
if(i>0)
return true;
return false;
}
public static int getCount(String sql, Object[] params){
conn=createConn();
int i=0;
try {
rs=executeQuery(sql,params);
if(rs.next()){
i=rs.getInt(1);
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
closeAll();
}
return i;
}
public static void Batch(String sql){
conn=createConn();
try {
ps=conn.prepareStatement(sql);
} catch (SQLException e) {
e.printStackTrace();
}
}
public static void addBatch(Object[] o){
try {
prepare(ps,o);
ps.addBatch();
} catch (SQLException e) {
e.printStackTrace();
}
}
public static void executeBatch(){
try {
ps.executeBatch();
} catch (SQLException e) {
e.printStackTrace();
}finally {
closeAll();
}
}
private static void prepare(PreparedStatement ps, Object[] params) throws SQLException {
if(params!=null){
for(int i=0;i<params.length;i++){
ps.setObject(i+1,params[i]);
}
}
}
public static void closeAll(){
try {
if(rs!=null)
rs.close();
if(ps!=null)
ps.close();
if(conn!=null)
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
public static ArrayList<HashMap<String, String>> getHashmap(String sql, Object[] params){
ArrayList<HashMap<String,String>> list=new ArrayList<>();
ResultSet rs = executeQuery(sql, params);
try {
ResultSetMetaData rsmd = rs.getMetaData();
int columnCount = rsmd.getColumnCount();
while (rs.next()){
HashMap<String, String> hashMap = new HashMap<>();
for(int i=1;i<=columnCount;i++){
hashMap.put(rsmd.getColumnLabel(i),rs.getString(i));
}
list.add(hashMap);
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
closeAll();
}
return list;
}
public static void main(String[] args) {
String sql="select patient.name as pname,recode.ordertime,recode.state, recode.id as rid,numsource.serialnumber,numsource.nstime,workday.worktime\n" +
"from workday,numsource,recode,patient where\n" +
"workday.doctorid=? and workday.id=numsource.workdayid and numsource.id=recode.nid and recode.pid=patient.id\n" +
"order by ordertime desc";
ArrayList<HashMap<String, String>> hashmap = getHashmap(sql, new Object[]{"1"});
for (int i=0;i<hashmap.size();i++){
System.out.println(hashmap.get(i));
}
}
}
mysql版本是5.7，但是报错提示com.mysql.cj.jdbc.xxx,idea测试数据库连接时也提示要设置时区。
但我用的是mysql5.7
java.sql.SQLException: Access denied for user 'root'@'localhost' (using password: NO)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:835)
at com.mysql.cj.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:455)
at com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:240)
at com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:207)
at java.sql.DriverManager.getConnection(DriverManager.java:664)
at java.sql.DriverManager.getConnection(DriverManager.java:247)
at util.DBUtil.createConn(DBUtil.java:40)
at util.DBUtil.executeQuery(DBUtil.java:47)
at dao.PatientDao.query(PatientDao.java:30)
at servlet.Login.service(Login.java:56)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:741)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at filter.MyFilter.doFilter(MyFilter.java:39)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:202)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:541)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:139)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92)
at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:688)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:74)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:343)
at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:367)
at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65)
at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:868)
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1639)
at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.lang.Thread.run(Thread.java:748)
