一、java链接mysql工具类
文件名:DataBaseUtil.java
package com.swu.util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
/**
* 连接数据库工具类
* @author cdgiser
* @version v1.0
* **/
public class DataBaseUtil {
public static Connection getConnection(){
Connection conn=null;
try {
Class.forName("com.mysql.jdbc.Driver");
String url="jdbc:mysql://192.168.1.103:3306/map";
String user="root";
String password="abc123";
conn=(Connection)DriverManager.getConnection(url,user,password);
} catch (Exception e) {
e.printStackTrace();
}
return conn;
}
public static List<HashMap<String,Object>>findLocations(){
List<HashMap<String,Object>> maps=new ArrayList<HashMap<String,Object>>();
Connection conn=null;
PreparedStatement statement=null;
ResultSet rs=null;
try{
conn=getConnection();
String sql="select * from test";
statement=(PreparedStatement)conn.prepareStatement(sql);
rs=(ResultSet)statement.executeQuery();
HashMap<String,Object>map=null;
while(rs.next()){
map=new HashMap<String,Object>();
map.put("id", rs.getInt("id"));
map.put("name", rs.getString("name"));
map.put("age", rs.getString("age"));
maps.add(map);
}
}catch(Exception e) {
e.printStackTrace();
}finally{
try{
if(rs!=null){
rs.close();
}
if(statement!=null){
statement.close();
}
if(conn!=null){
conn.close();
}
}catch(Exception e) {
e.printStackTrace();
}
}
return maps;
}
//java 入口
public static void main(String[] args) {
System.out.println("okokok");
List<HashMap<String,Object>> datas=findLocations();
for(HashMap<String,Object>map:datas){
System.out.println(map.get("id"));
System.out.println(map.get("name"));
System.out.println(map.get("age"));
}
}
}
二、记得导入链接mysql的jar包。放在lib文件夹下。mysql-connector-java-5.1.22-bin.jar。。。
http://download.csdn.net/detail/orzma/4828533
三、console报错:java.sql.SQLExcept "Host 'Lenovo-PC' is not allowed to connect to this MySQL server"
远程java链接mysql数据库报错。。。
大家从上面这段信息中可以看出当使用h参数为本机的ip地址时,mysql不允许该客户端连接,这是为什么呢?
看下面这段sql查询:
1
2
3
4
5
6
7
8
9
10
11
12
|
mysql> use mysql
Database changed
mysql>
select
user,host from user;
+------+-----------+
| user | host |
+------+-----------+
| root | 127.0.0.1 |
| root | ::1 |
| | localhost |
| root | localhost |
+------+-----------+
4 rows
in
set
(0.00 sec)
|
root用户只允许在127.0.0.1和localhost上进行连接,而不允许使用本机在局域网中的ip进行连接。。。
这样就明白为什么不允许连接的原因了。。。
下面就要给root用户相应的登陆权限
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
mysql>
GRANT
ALL
PRIVILEGES
ON
*.*
TO
'root'
@
'10.255.9.79'
IDENTIFIED
BY
'034039'
WITH
GRANT
OPTION
;
Query OK, 0
rows
affected (0.23 sec)
mysql>
select
user
,host
from
user
;
+
------+-------------+
|
user
| host |
+
------+-------------+
| root | 10.255.9.79 |
| root | 127.0.0.1 |
| root | ::1 |
| | localhost |
| root | localhost |
+
------+-------------+
5
rows
in
set
(0.00 sec)
mysql>
|
通过这条授权语句,
1
|
mysql>
GRANT
ALL
PRIVILEGES
ON
*.*
TO
'root'
@
'10.255.9.79'
IDENTIFIED
BY
'034039'
WITH
GRANT
OPTION
;
|
允许地址10.255.9.79上用root用户,密码034039来连接mysql的所有数据库,付给select,insert,update,delete权限。
最后刷新一下权限:
1
2
|
mysql> flush
privileges
;
Query OK, 0
rows
affected (0.68 sec)
|
OK,这样就完成了授权。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
C:\Users\Lenovo>mysql -h 10.255.9.79 -u root -p034039
Warning: Using a
password
on
the command line interface can be insecure.
Welcome
to
the MySQL monitor. Commands
end
with
;
or
\g.
Your MySQL
connection
id
is
55
Server version: 5.6.21 MySQL Community Server (GPL)
Copyright (c) 2000, 2014, Oracle
and
/
or
its affiliates.
All
rights reserved.
Oracle
is
a registered trademark
of
Oracle Corporation
and
/
or
its
affiliates. Other names may be trademarks
of
their respective
owners.
Type
'help;'
or
'\h'
for
help. Type
'\c'
to
clear the
current
input statement.
mysql>
|
=======END=======
转:http://my.oschina.net/xinxingegeya/blog/208544