java中要连接mysql首先需要下载mysql驱动,然后在项目中添加对mysql驱动的引用。
然后需要在mysql中建表,我测试中用的表定义如下:
CREATE TABLE `test_notebook` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(60) NOT NULL,
`content` longtext,
`createtime` datetime NOT NULL,
`ip` varchar(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8$$
然后就是代码了,如下测试代码:
package com.yukaizhao;
import java.sql.*;
public class HelloDatabase {
public static void main(String[] args){
Connection conn = null;
try{
conn = getConnection();
System.out.println("getConnection successfully");
//查询
String sql = "select * from test_notebook";
//准备执行,设置sql参数
PreparedStatement statement = conn.prepareStatement(sql);
//执行查询
ResultSet result = statement.executeQuery();
//获得数据
for(boolean valid = result.first();valid ;valid = result.next()){
int id = result.getInt("id");
String name = result.getString("name");
String content = result.getString("content");
Date date = result.getDate("createtime");
String ip = result.getString("ip");
System.out.println(id);
System.out.println(name);
System.out.println(content);
System.out.println(date);
System.out.println(ip);
}
//关闭statement
statement.close();
//向数据库中插入数据
String sqlInsert = "INSERT INTO `notes`.`test_notebook`(`name`,`content`,`createtime`,`ip`)VALUES(?,?,?,?)";
PreparedStatement insertState = conn.prepareStatement(sqlInsert);
insertState.setString(1, "hello java");
insertState.setString(2,null);
java.util.Date now = new java.util.Date();
//java.sql.Date sqlNow = new java.sql.Date(now.getTime());
//insertState.setDate(3,sqlNow);
//java.sql.Time sqlTime = new java.sql.Time(now.getTime());
//insertState.setTime(3, sqlTime);
//注意数据库中的datetime字段对应的是java.sql.Timestamp,而java.sql.Date会只保留日期部分,而java.sql.Time则只是时间部分
java.sql.Timestamp sqlTimeStamp = new java.sql.Timestamp(now.getTime());
insertState.setTimestamp(3, sqlTimeStamp);
insertState.setString(4, "127.0.0.1");
insertState.executeUpdate();
//获得最新插入数据的id
String sqlIdentity = "SELECT last_insert_id() as lastid";
Statement idState = conn.createStatement();
ResultSet rsId = idState.executeQuery(sqlIdentity);
rsId.first();
int id = rsId.getInt(1);
System.out.println(String.format("newid is %d",id));
}catch(Exception ex){
ex.printStackTrace();
}finally{
if(conn != null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
conn = null;
}
}
}
static Connection getConnection()
throws InstantiationException,
IllegalAccessException,
ClassNotFoundException,
SQLException{
Class.forName("com.mysql.jdbc.Driver").newInstance();
String defaultDomain = "localhost:3306";
String defaultName = "notes";
String defaultUser = "root";
String defaultPass = "root";
Connection conn = DriverManager.getConnection("jdbc:mysql://" + defaultDomain + "/" + defaultName + "?user=" + defaultUser + "&password=" + defaultPass);
return conn;
}
}
java数据库操作时需要注意日期时间,代码中做了注释。