一个jdbc连接mysql的小例子

 

创建数据库

指定数据库字符集和校对规则：

mysql> create database it315 default character set gb2312 collate gb2312_chinese_ci;

 

进入刚创建的数据库:

mysql> use it315;

Database changed

 

创建表:

mysql> create table student (id int primary key,name varchar(20),address varchar(20));

 

写一个属性文件: jdbc_mysql.properties,此文件存储一些连接相关的信息:

driver=com.mysql.jdbc.Driver             //驱动名称

url=jdbc:mysql://localhost:3306/it315    //指定URL

user=root                                                 //用户名

password=                                        //密码

characterEncoding=gb2312                 //指定字符编码

 

写java源文件: JdbcMySql.java

import java.io.IOException;

import java.io.InputStream;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.Statement;

import java.util.Properties;

 

public class JdbcMySql {

 

       public static Properties getProperties() {

              Properties props = new Properties();

              InputStream is = null;

              try {

                     is = JdbcMySql.class

                                   .getResourceAsStream("/jdbc_mysql.properties");

                     props.load(is);

              } catch (Exception ex) {

                     ex.printStackTrace();

return null;

              } finally {

                     if (is != null)

                            try {

                                   is.close();

                            } catch (IOException e) {

                                   e.printStackTrace();

                            }

              }

              return props;

       }

 

       public static void main(String[] args) {

              Connection conn = null;

              Statement stmt = null;

              ResultSet rs = null;

 

              Properties props = getProperties();

              if (props != null){

                    //读取出属性文件里面的内容

                    String driver = props.getProperty("driver");

                    String url = props.getProperty("url");

                     String user = props.getProperty("user");

                    String password = props.getProperty("password");

                    String characterEncoding = props.getProperty("characterEncoding");

 

                    try {

                            Class.forName(driver);//加载驱动

 

                            conn = DriverManager.getConnection(url + "?characterEncoding="

                                          + characterEncoding, user, password);//建立连接

                            stmt = conn.createStatement();

 

                            String sql = "insert into student values(1,'张三','湖南')";

                            stmt.executeUpdate(sql);//执行sql语句

 

                            sql = "select * from Student";

                            rs = stmt.executeQuery(sql);

 

                            while (rs.next()) {//从结果集中取出数据

                                   System.out.print(rs.getInt(1) + "\t");

                                   System.out.print(rs.getString(2) + "\t");

                                   System.out.print(rs.getString(3) + "\n");

                            }

 

                     } catch (Exception e) {

                            e.printStackTrace();

                     } finally {//释放连接

                            try {

                                   if (rs != null)

                                          rs.close();

                                   if (stmt != null)

                                          stmt.close();

                                   if (conn != null)

                                          conn.close();

                            } catch (Exception ex) {

                                   ex.printStackTrace();

                            }

                     }

}

       }

}

 

编译运行:

C:\ >javac JdbcMySql.java

 

C:\ >java JdbcMySql

1                         张三 湖南

 

进数据库查询刚刚插入的信息:

mysql> select * from student;

+----+------+---------+

| id | name | address |

+----+------+---------+

| 1 | ??? | ??    |

+----+------+---------+

我们看到中文变成了问号.别急!退出mysql

mysql> quit;

Bye

 

再进去:

C:\ >mysql -uroot --default-character-set=gb2312

 

查询:

mysql> use it315;

Database changed

mysql> select * from student;

+----+--------+---------+

| id | name   | address |

+----+--------+---------+

| 1 |张三 | 湖南 |

+----+--------+---------+

OK!一切正常了….