JDBC原生查询

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.Statement;

import java.util.ArrayList;

import java.util.List;

public class Test {

public static Connection getCon() {

//数据库连接名称

      String username="root";

//数据库连接密码

      String password="123456";

String driver="com.mysql.jdbc.Driver";

//其中test为数据库名称

      String url="jdbc:mysql://127.0.0.1:3306/mqhlyw_bsp";

Connection conn=null;

try{

Class.forName(driver);

conn=(Connection) DriverManager.getConnection(url,username,password);

}catch(Exception e){

e.printStackTrace();

}

return conn;

}

private  static boolean existLoginName(String loginName){

String sql ="select * from bsp_user u where u.login_name='"+loginName+"'";

// 获取到连接

      Connection conn =getCon();

try {

Statement sta = conn.createStatement();

ResultSet res = sta.executeQuery(sql);

if(res.next()){

return true;

}

sta.close();

conn.close();

}catch (Exception e) {

e.printStackTrace();

}

return false;

}

private static List findRoles(String loginName){

List mapList =new ArrayList();

String sql ="select r.role_key from bsp_user_role ur "+

" LEFT JOIN bsp_user u ON ur.user_id=u.id "+

" LEFT JOIN bsp_role r ON ur.role_id=r.id "+

" where  u.login_name='"+loginName+"'";

// 获取到连接

      Connection conn =getCon();

try {

Statement sta = conn.createStatement();

ResultSet res = sta.executeQuery(sql);

while(res.next())

{

mapList.add(res.getString("role_key"));

}

sta.close();

conn.close();

}catch (Exception e) {

e.printStackTrace();

}

return mapList;

}

public static void main(String[] args) {

List list=findRoles("zhang3");

for (String string : list) {

System.out.println("qqqqqqqqqqqqqq"+string);

}

/*boolean b=existLoginName("zhang3");

System.out.println("aaaaaaaaaaa"+b);*/

  }

}