Mysql连接和操作

上一篇 / 下一篇  2019-04-17 19:49:21 / 个人分类:java基础

1、创建数据库连接方法:
public classMysqlDao {

public staticConnectiongetConnention(String dbIP,String dbPort,String dbName,String userName,String password,String args){
Connection conn;
try{
// 加载驱动
Class.forName("com.mysql.jdbc.Driver");
String url ="jdbc:mysql://"+ dbIP +":"+ dbPort +"/"+ dbName + args;
//连接数据库
conn = DriverManager.getConnection(url,userName,password);
}catch(Exception var0){
System.out.println("连接异常"+var0);
throw newRuntimeException(var0.getMessage());
}
returnconn;
}
}

Class.forName("XXX") ----> 加载指定数据库的渠道
url ---->拼装连接数据库的地址信息,如果是Mysql的数据库,开头是使用“jdbc:mysql”
DriverManager.getConnection(url,userName,password) ---->连接指定的数据库

2、创建操作数据库的方法:
(以下只是用preparedStatement为例,因为preparedStatement比createStatement的性能要好)
说明:preparedStatement提供了execute、executeUpdate、executQuary三个方法。 execute支持所有操作,executeUpdate支持增加、删除、更新,executQuary只支持查询操作。
public classBaseDao {

publicBaseDao(){

}


/**
*@Description:执行新增、删除、更新操作,并且返回影响数目
*@Parma:
*@return:
*/
public static intexecuteUpdate(Connection conn,String sql,Object... params){
PreparedStatement pstmt =null;
intcount =0;
try{
pstmt = conn.prepareStatement(sql);
for(inti =0;i < params.length;i++){
pstmt.setObject(i+1,params[i]);
}
count = pstmt.executeUpdate();
}catch(SQLException var5){
throw newRuntimeException(var5.getMessage());
}
finally{
closePreparement(pstmt);
}

returncount;
}

/**
*@Description:获取指定查询语句的第一条数据
*@Parma:Connection conn:数据库连接
* String sql:查询语句
* Object... params:对查询语句中指定占位符进行批量替换
*@return:
*/
public staticMap<String,Object>singlSearchByParams(Connection conn,String sql,Object... params){
List<Map<String,Object>> resultList =searchListByParams(conn,sql,params);
if(null== resultList || resultList.size() <=0){
Object var1 =null;
return(Map)var1;
}
Map<String,Object> map = resultList.get(0);
returnmap;
}

public staticMap<String,Object>singlSearchByParams(Connection conn,String sql){
returnsinglSearchByParams(conn,sql,null);
}

public staticList<Map<String,Object>>searchListByParams(Connection conn,String sql,Object... params){
PreparedStatement pstmt =null;
ResultSet rs =null;
List<Map<String,Object>> resultList =newArrayList();
try{
pstmt = conn.prepareStatement(sql);
if(null!= params) {
for(inti =0;i < params.length;++i) {
pstmt.setObject(i +1,params[i]);
}
}

rs = pstmt.executeQuery();
resultList =getResultMap(rs);
if(null== resultList || resultList.size() <=0){
Object var4 =null;
return(List)var4;
}
}catch(SQLException var0){
throw newRuntimeException(var0.getMessage());
}
finally{
if(null!= rs){
closeResultSet(rs);
}
if(null!= pstmt){
closePreparement(pstmt);
}
}

returnresultList;
}


public staticList<Map<String,Object>>getResultMap(ResultSet rs)throwsSQLException{
List<Map<String,Object>> resultList =newArrayList();
ResultSetMetaData rsmd = rs.getMetaData();
intcount = rsmd.getColumnCount();

while(rs.next()){
Map<String,Object> map =newHashMap();

for(inti =1;i < count;i++){
String key = rsmd.getColumnLabel(i);
Object value = rs.getObject(i);
map.put(key,value);
}
resultList.add(map);
}
returnresultList;
}

//TODO 如何改为使用DbUilt
public static voidcloseResultSet(ResultSet rs){
try{
rs.close();
}catch(SQLException var2){
;
}
}

public static voidclosePreparement(PreparedStatement pstmt){
try{
pstmt.close();
}catch(SQLException var3){
;
}
}



}

2.1、执行一个查询语句:
2.1.1、看searchListByParams(Connection conn, String sql, Object... params)方法
pstmt = conn.prepareStatement(sql);----》创建一个PreparedStatement对象
rs = pstmt.executeQuery();  ----》执行sql语句的操作
2.1.2、对查询回来的ResultSet数据整理为List<Map<String,Object>>
看getResultMap(ResultSet rs)方法
ResultSetMetaData rsmd = rs.getMetaData(); ----》通过getMetaData()获取对应的ResultSetMetaData,为了是获取每一个数据列的信息
int count = rsmd.getColumnCount(); ----》获取列数
while(rs.next()){……} ----->把数据组装成List<Map<String,Object>>, 注意:rsmd.getColumnLabel(i);是从第1位开始取


3、最后我们来测试
public classtest {

@Test
public voidtest(){
Connection conn = MysqlDao.getConnention("192.168.10.97","3006","applicat_center","root12","12345678","");
Map<String,Object> map = BaseDao.singlSearchByParams(conn,"SELECT * FROM open_application WHERE product_code =\"ABCD\"ORDER BY apply_time DESC limit 1");
System.out.println("查询结果:"+map.toString());
}
}




遇到过的问题:
1、问题:调到Class.forName("XXX")报错:java.lang.ClassNotFoundException: com.mysql.jdbc.Driver
解决:在pom.xml添加配置
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>6.0.5</version>
</dependency>





TAG: 连接 MySQL

 

评分:0

我来说两句

我的栏目

日历

« 2024-03-24  
     12
3456789
10111213141516
17181920212223
24252627282930
31      

数据统计

  • 访问量: 996
  • 日志数: 2
  • 建立时间: 2015-01-05
  • 更新时间: 2019-04-18

RSS订阅

Open Toolbar