Linux 下使用Java连接MySQL数据库,并且实现插入、删除、选择操作
实验环境:
OS : CentOS5.5 (Linux)
IDE : Eclipse
DBMS : MySQL
准备工作:
1、安装 MySql 。
详见http://www.cnblogs.com/zyumeng/archive/2012/09/17/2688772.html
2、安装 JDBC :
在命令行中输入:
wegt http://www.mysql.com/downloads/connector/j/mysql-connector-java-5.1.22.tar.gz
tar xzvf mysql-connector-java-5.1.22.tar.gz
3、将 mysql 服务器启动起来:
service mysqld start
4、登陆服务器以建立数据库:
mysql –u root –ppassword
5、建立数据库:
create database Data;
然后即可在 IDE (Eclipse)下实现连接。
6、在Eclipse建立项目,然后 将驱动加载到该项目上。
方法为 : 点击项目右键 ->Properties( 属性 )->Java Build Path->Libraries->Add External JARs :
选择“解压JDBC目录”/mysql-connector-java-5.1.15.jar
在Eclipse中建立Java文件,命名为DataBase.java
具体代码:
import java.io.UnsupportedEncodingException ;
import java.sql.Connection ;
import java.sql.DriverManager ;
import java.sql.PreparedStatement ;
import java.sql.ResultSet ;
import java.sql.SQLException ;
import java.sql.Statement ;
public class DataBase
{
public static final String driver = "com.mysql.jdbc.Driver" ; // 驱动
public static final String url = "jdbc:mysql://localhost:3306/Data" ;// 定义URL
public static final String user = "root" ; // 用户名字
public static final String password = "password" ; // 用户密码
private static Connection connection ;// 用于建立连接
private static Statement statement ;// 用于执行
public static void main( String[] args ) throws ClassNotFoundException ,
SQLException , UnsupportedEncodingException
{
connect() ;// 先和数据库服务器建立连接
createTable() ;// 建立表
insert() ;// 向表中插入值
query() ;// 查询
close() ;// 关闭连接
}
/**
* @throws ClassNotFoundException
* @throws SQLException
* 此方法建立连接
*/
private static void connect() throws ClassNotFoundException , SQLException
{
Class.forName( driver ) ;
connection = DriverManager.getConnection( url , user , password ) ;// 建立连接
if( !connection.isClosed() )
{
System.out.println( "Succeeded connecting to the Database!" ) ;
}
statement = connection.createStatement() ;// 建立statement
}
/**
* @throws SQLException
* 建立表
*/
private static void createTable() throws SQLException
{
String sql = "create table Student( Id char(20) not null , Name char(20) not null , Sex char(10) not null , Mail char(30) not null , Adress char(30) not null , primary key(Id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;" ;
statement.executeUpdate( "drop table if exists Student ;" ) ;// 如果该表已经有了则删除
statement.executeUpdate( sql ) ;// 建立表
}
/**
* @throws UnsupportedEncodingException
* @throws SQLException
* 插入值
*/
private static void insert() throws UnsupportedEncodingException ,
SQLException
{
// 声明常量
String[] ids = { "123" , "456" , "789" } ;
String[] names = { "张三" , "李四" , "王五" } ;
String[] sexs = { "女" , "男" , "男" } ;
String[] mails = { "123@qq.com" , "456@qq.com" , "789@qq.com" } ;
String[] addresses = { "北京" , "上海" , "深圳" } ;
String id = null , name = null , sex = null , mail = null , address = null ;
String sql = "insert into Student( Id , Name , Sex , Mail , Adress ) values( ? , ? , ? , ? , ? ) ;" ;
PreparedStatement preparedStatement = connection.prepareStatement( sql ) ;
for( int i = 0 ; i < 3 ; ++ i )
{
id = ids[ i ] ;
name = new String( names[ i ].getBytes( "gbk" ) , "ISO-8859-1" ) ;// 因为中文会出现乱码,因此先转码
sex = new String( sexs[ i ].getBytes( "gbk" ) , "ISO-8859-1" ) ;
mail = mails[ i ] ;
address = new String( addresses[ i ].getBytes( "gbk" ) ,
"ISO-8859-1" ) ;
preparedStatement.setString( 1 , id ) ;
preparedStatement.setString( 2 , name ) ;
preparedStatement.setString( 3 , sex ) ;
preparedStatement.setString( 4 , mail ) ;
preparedStatement.setString( 5 , address ) ;
preparedStatement.executeUpdate() ;// 执行
}
}
/**
* @throws SQLException
* @throws UnsupportedEncodingException
* 查询
*/
private static void query() throws SQLException ,
UnsupportedEncodingException
{
String query = "select * from Student" ;
ResultSet resultset = null ;
resultset = statement.executeQuery( query ) ;// 先将结果保存到resultset中
System.out.println( "Id/tName/tSex/tMail/t/tAddress" ) ;
System.out
.println( "---------------------------------------------------------------" ) ;
// 将结果读出来
while( resultset.next() )
{
String id = resultset.getString( "Id" ) ;
String name = new String( resultset.getString( "Name" ).getBytes(
"ISO-8859-1" ) , "gbk" ) ;// 将码再转回来,以防乱码
String sex = new String( resultset.getString( "Sex" ).getBytes(
"ISO-8859-1" ) , "gbk" ) ;
String mail = resultset.getString( "Mail" ) ;
String address = new String( resultset.getString( "Adress" )
.getBytes( "ISO-8859-1" ) , "gbk" ) ;
System.out.println( id + "/t" + name + "/t" + sex + "/t" + mail
+ "/t" + address ) ;
}
System.out
.println( "---------------------------------------------------------------" ) ;
}
/**
* @throws SQLException
* 关闭连接
*/
private static void close() throws SQLException
{
statement.close() ;// 关闭statement
connection.close() ;// 关闭连接
}
}
已验证上述程序正确
参考资料:
http://blog.csdn.net/shiyanhui66/article/details/6382483
《MySQL快速入门》 第18章 清华大学出版社
转载出处:https://www.cnblogs.com/zyumeng/archive/2012/10/09/2716370.html