I am using a video course on database programming with the current lesson being using Java to connect to MySQL. I have followed the video, and even copied the text working file for this particular problem (so I know the code works), but I am still getting an error. The database is to store information for books: isbn, title, author, publisher, and price. I inserted the exact same data using the command line, but when I use the program for a GUI I get a "data truncated" error. I know there are multiple answers in "data truncated" errors; however, I do not see where the data is too large, especially when inserting works using a non GUI interface. All datatypes are VARCHAR except for price which is FLOAT. The error I get is:
insert into book values('978007106789','Stuck On Java','J Reid','9.99','Osborne') Error executing SQL java.sql.SQLException: Data truncated for column 'price' at row 1
GUI code is:
package Connection;
import java.awt.*;
import java.awt.event.*;
import javax.swing.*;
import java.sql.*;
import java.util.*;
public class InsertRecord extends JFrame {
private JButton getBookButton, insertBookButton;
private JList bookList;
private Connection connection;
private JTextField isbn, title, author, price, publisher;
private JTextArea errorText;
public InsertRecord() {
try {
Class.forName("com.mysql.jdbc.Driver").newInstance();
}
catch (Exception e) {
System.err.println("Unable to load driver.");
System.exit(1);
}
}
public void loadBook() {
Vector<String> v = new Vector<String>();
try {
Statement statement = connection.createStatement();
ResultSet rs = statement.executeQuery("select title from book");
while (rs.next()) {
v.addElement(rs.getString("title"));
}
rs.close();
}
catch (SQLException e) {
System.err.println("Error executing SQL");
}
bookList.setListData(v);
}
private void createGUI() {
Container c = getContentPane();
c.setLayout(new FlowLayout());
bookList = new JList();
loadBook();
bookList.setVisibleRowCount(2);
JScrollPane bookListScrollPane = new JScrollPane(bookList);
getBookButton = new JButton("Get Book Title");
getBookButton.addActionListener(
new ActionListener() {
public void actionPerformed(ActionEvent e) {
String query = "select * from book where title = " +
bookList.getSelectedValue();
try {
Statement statement = connection.createStatement();
ResultSet rs = statement.executeQuery(
"select * from book where title = '"
+ bookList.getSelectedValue() + "'");
/*ResultSet rs = statement.executeQuery(
"select * from book where title = 'Java:How To Program'"); */
if (rs.next()) {
isbn.setText(rs.getString("isbn"));
title.setText(rs.getString("title"));
author.setText(rs.getString("author"));
price.setText(rs.getString("price"));
publisher.setText(rs.getString("publisher"));
}
}
catch (SQLException ex) { isbn.setText(query); }
}
}
);
insertBookButton = new JButton("Insert Book");
insertBookButton.addActionListener (
new ActionListener() {
public void actionPerformed(ActionEvent e) {
try {
Statement statement = connection.createStatement();
String insert = "insert into book values(";
insert += "'" + isbn.getText() + "',";
insert += "'" + title.getText() + "',";
insert += "'" + author.getText() + "',";
insert += "'" + price.getText() + "',";
insert += "'" + publisher.getText() + "')";
System.out.println(insert);
/*int i = statement.executeUpdate("insert into book values(" +
"'" + isbn.getText() + "'," +
"'" + title.getText() + "'," +
"'" + author.getText() + "'," +
"'" + price.getText() + "'," +
"'" + publisher.getText() + ")");*/
int i = statement.executeUpdate(insert);
errorText.append("Inserted " + i + " rows succcessfully.");
bookList.removeAll();
loadBook();
}
catch (SQLException ex) {
System.err.println("Error executing SQL");
ex.printStackTrace();
}
}
}
);
JPanel first = new JPanel(new GridLayout(3,1));
first.add(bookListScrollPane);
first.add(getBookButton);
first.add(insertBookButton);
isbn = new JTextField(13);
title = new JTextField(50);
author = new JTextField(50);
price = new JTextField(8);
publisher = new JTextField(50);
errorText = new JTextArea(5,15);
errorText.setEditable(false);
JPanel second = new JPanel();
second.setLayout(new GridLayout(6,1));
second.add(isbn);
second.add(title);
second.add(author);
second.add(price);
second.add(publisher);
JPanel third = new JPanel();
third.add(new JScrollPane(errorText));
c.add(first);
c.add(second);
c.add(third);
setSize(800, 400);
setVisible(true);
}
public void connectToDB() throws Exception {
//Connection conn = null;
try {
String userName = "jesse";
String password = "password";
String url = "jdbc:mysql://localhost/library";
Class.forName("com.mysql.jdbc.Driver").newInstance();
connection = DriverManager.getConnection(url, userName, password);
//if (conn != null) System.out.println("Database connection successful.");
}
catch (SQLException e) {
System.out.println("Can't connect to database");
System.exit(1);
}
}
private void init() throws Exception{
connectToDB();
}
public static void main(String[] args) throws Exception {
InsertRecord insert = new InsertRecord();
insert.addWindowListener(
new WindowAdapter() {
public void windowClosing(WindowEvent e) {
System.exit(0);
}
}
);
insert.init();
insert.createGUI();
}
}
The insert code for simply using the command line is:
package Connection;
import java.sql.*;
import java.io.*;
public class InsertDB {
Connection connection;
public InsertDB(){
try {
Class.forName("com.mysql.jdbc.Driver").newInstance();
}
catch (Exception e) {
System.out.println("Could not load driver.");
e.printStackTrace();
}
}
public void ConnectToDB() {
try {
connection = DriverManager.getConnection("jdbc:mysql://localhost/library", "jesse", "password");
System.out.println("Connected to database.");
}
catch (Exception e) {
System.out.println("Cannot connect to database.");
e.printStackTrace();
}
}
public void execSQL() {
try {
Statement stmt = connection.createStatement();
BufferedReader input = new BufferedReader(new InputStreamReader(System.in));
System.out.print("Enter the isbn: ");
String isbn = input.readLine();
System.out.print("Enter the title: ");
String title = input.readLine();
System.out.print("Enter the author: ");
String author = input.readLine();
System.out.print("Enter the publisher: ");
String pub = input.readLine();
System.out.print("Enter the price: ");
String p = input.readLine();
double price = Double.parseDouble(p);
String insert = "Insert into book values (" + "'" + isbn + "','" + title + "','" + author + "','" + pub + "'," + price + ")";
System.out.println(insert);
int inserted = stmt.executeUpdate(insert); //returns 1 for success, 0 for failure
if (inserted > 0) {
System.out.println("Successfully inserted " + inserted + " row.");
}
}
catch (Exception e) {
System.out.println("Error executing SQL");
e.printStackTrace();
}
}
public static void main(String[] args){
InsertDB conn = new InsertDB();
conn.ConnectToDB();
conn.execSQL();
}
}
The only differences I have noticed is price being in quotes in the GUI code; however, removing the quotes simply causes the same error without quotes. Also I noticed that the GUI code sets price to 8 bits (original code was 10), whereas, float is not set to anything in MySQL (I believe I read on another post it is 8 bits by default... which is why I used 8). I reached out to the author of the video and he suggested I remove the quotes surrounding price. But as I stated this did not help... also this code was copied from his working file that worked on the video. Any help is appreciated.
Database code is:
drop table book;
create table book (
isbn_13 varchar(13) primary key,
title varchar(50),
author varchar(50),
publisher varchar(50),
price float(11)
);
