Java数据库操作入门
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);}//关闭statementstatement.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();//获得最新插入数据的idString 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()throwsInstantiationException, 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数据库操作时需要注意日期时间，代码中做了注释。
页:
[1]