jdbc java例子_Java JDBC 小例子

1. 使用MySQL建立一个test数据库，里面建立一个mytable表，3列(id，name，age)；将url, user, password配制成properties文件，放到 工程resource源代码包下面，这里将其命名为db_connect.properties文件

url = jdbc:mysql://localhost:3306/test

user = root

password = admin

2. 将数据库连接封装到一个类中，利用配置文件连接，静态返回connection

package study.jdbc;

import java.io.FileInputStream;

import java.io.FileNotFoundException;

import java.io.IOException;

import java.io.InputStream;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.SQLException;

import java.util.Properties;

public class DBConnect {

static String url;

static String user;

static String password;

/**

* 获取一个JDBC连接，返回一个Connection对象

* @return connection

*/

public static Connection connectDB() {

Connection connection = null;

readProperties();

try {

Class.forName("com.mysql.jdbc.Driver");

connection = DriverManager.getConnection(url, user, password);

} catch (SQLException e) {

e.printStackTrace();

} catch (ClassNotFoundException e) {

e.printStackTrace();

}

return connection;

}

/**

* 读取properties文件，获取url,user,password

*/

private static void readProperties() {

String fileName = "resouce/db_connect.properties"; //相对于工程

Properties properties = new Properties();

try {

InputStream in = new FileInputStream(fileName);

properties.load(in);

in.close();

} catch (FileNotFoundException e) {

e.printStackTrace();

} catch (IOException e) {

e.printStackTrace();

}

url = properties.getProperty("url");

user = properties.getProperty("user");

password = properties.getProperty("password");

}

}

3.  对数据库进行增删改查的测试，主要练习使用PreparedStatement

package study.jdbc;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

public class TestMain {

public static void main(String[] args) {

Connection connection = DBConnect.connectDB(); //获取数据库连接

TestMain test = new TestMain();

try { //测试

test.clear(connection);

test.insert(connection); //增

test.query(connection);

System.out.println("----------");

test.delete(connection); //删

test.query(connection);

System.out.println("----------");

test.update(connection); //改

test.query(connection); //查

connection.close(); //关闭数据库连接

} catch (SQLException e) {

e.printStackTrace();

}

}

/**

* 使用PreparedStatement，效率高

* 动态执行SQL(带参数的SQL语句),是Statement子接口

* 对数据库进行insert，用带参数的语句批量插入

* @param connection

* @throws SQLException

*/

public void insert(Connection connection) throws SQLException {

String sql = "INSERT INTO mytable(id,name,age) values (?,?,22);";

PreparedStatement pr = connection.prepareStatement(sql);

for (int i = 1; i <= 3; i++) {

pr.setInt(1, i);

pr.setString(2, "demo"+i);

pr.executeUpdate();

}

}

/**

* 对数据库进行delete

* @param connection

* @throws SQLException

*/

public void delete(Connection connection) throws SQLException {

String sql = "delete from mytable where id=2;";

PreparedStatement pr = connection.prepareStatement(sql);

pr.executeUpdate();

}

/**

* 对数据库进行update

* @param connection

* @throws SQLException

*/

public void update(Connection connection) throws SQLException {

String sql = "UPDATE mytable SET name='new' WHERE id=1;";

PreparedStatement pr = connection.prepareStatement(sql); // 创建statement对象发送SQL到数据库

pr.executeUpdate(); // 执行UPDATE

}

/**

* 查询query遍历结果集

* @param connection

* @throws SQLException

*/

public void query(Connection connection) throws SQLException {

String sql = "select * from mytable;";

PreparedStatement st = connection.prepareStatement(sql);

ResultSet re = st.executeQuery(); // 查询，返回单个ResultSet对象

while (re.next()) {

int id = re.getInt(1);

String name = re.getString(2);

int age = re.getInt(3);

System.out.println(id + "\t" + name + "\t" + age);

}// 遍历结果集

}

/**

* 清空表

* @param connection

* @throws SQLException

*/

public void clear(Connection connection) throws SQLException {

String sql = "delete from mytable;";

PreparedStatement pr = connection.prepareStatement(sql);

pr.executeUpdate();

}

}