Database insertion

Question

if(lines.size() >= 5){
    String Actor  = it.next();
    String Bio = it.next();
    String More_Bio = it.next();
    String Reason = it.next();
    String Fact = it.next(); 

    if ( it.hasNext()== true &&it.next().startsWith("Actor : ") )
    {


         // for quotes

      Actor = Actor.replace("'", "''");
         // remove comment
      Actor = Actor.replace("Actor:  ", " ");

         System.out.println(Actor);


    }

    if ( it.hasNext()== true &&it.next().startsWith("Bio: ") )
    {

      Bio = Bio.replace("'", "''");
      Bio = Bio.replace("Bio:  ", "");
      System.out.println(Bio);

    }

     if (it.hasNext()== true &&it.next().startsWith("More_Bio: "))
    { 
    More_Bio = More_Bio.replace("'", "''");
    More_Bio = More_Bio.replace("More_Bio:  ", "");
    System.out.println(More_Bio);

    }
     if (it.hasNext()== true &&it.next().startsWith("Reason: ") )
    { 
    Reason = Reason.replace("'", "''");
    Reason = Reason.replace("Reason:  ", "");
    System.out.println(Reason);

    }
    if (it.hasNext()== true &&it.next().startsWith("Fact: ") )
    { 
   Fact =Fact.replace("'", "''");
   Fact =Fact.replace("Fact:  ", "");
    System.out.println(Fact);

    }

    Statement statement = con.createStatement();
    statement.executeUpdate("INSERT INTO Tiffany (Actor, Bio, More_Bio, Reason,Fact) values('"+Actor+"','"+Bio+"','"+More_Bio+"','"+Reason+"','"+Fact+"')");

File that is read from Actor : Zac Efron

Bio : He was born in San Luis Obispo, California, and raised nearby in Arroyo Grande, After guest-starring in several episodes of "Summerland" (2004), he joined the regular cast as girl-crazy Cameron Bale. Efron also starred in several pilots such as The Big Wide World of Carl Laemke (2003) (TV) and Triple Play (2004) (TV).

More_Bio : Efron graduated Arroyo Grande High School in June 2006. Efron's favorite sports include golf, skiing, rock climbing, and snowboarding. He recently added surfing after spending days on the beach for "Summerland."

Reason : I had a crush on this gorgeous, nice, talented actor since I'd first seen him in "High School Musical" and "Hairspray," and he's even hotter now. He's the reigning prince of hot in Hollywood.

Fact : Zac's most prized possession is his autographed baseball collection and he is s a huge San Francisco Giants fan.

Actor : Taylor Lautner

Bio : Taylor Daniel Lautner was born in Grand Rapids, Michigan to parents, Deborah and Daniel Lautner. He, and younger sister Makena, were raised in a well-mannered, Roman Catholic household in Hudsonville, Michigan.

More_Bio : However, in addition to his love for martial arts, Taylor quickly developed a love for acting at the age of seven years old when his martial arts instructor, who was involved in show business, encouraged him to audition for a small appearance in a Burger King commercial.

Reason : This is one hunky teen idol! I loved him as Jacob Black in the "Twilight" series! He is one of the best-looking guys I've ever seen. I was so excited when I tweeted him and he replied back once!

Fact : He played football during his freshman and sophomore year of high school. He is of German, French, Dutch, and Native American (specifically Ottawa and Potawatomi) descent. Omg! And we both like the band Kings of Leon.

I am trying the file above into a database. But this is the error I get when i run it.

Exception in thread "main" com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 's favorite sports include golf, skiing, rock climbing, and snowboarding. 

He rece' at line 1
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
    at com.mysql.jdbc.Util.getInstance(Util.java:381)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1030)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3491)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3423)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1936)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2060)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2536)
    at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1564)
    at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1485)
    at TiffanyWriter.main(TiffanyWriter.java:109)

try printing out what you are sending to mysql and proofreading that manually. — Jakob Weisblat, Jul 11 '12 at 20:53

score 3 · Accepted Answer · answered Jul 11 '12 at 21:13

You should use PreparedStatement primarily because it prevents SQL injection attacks. @John Moses has posted a tutorial to use PreparedStatement from the Java official documentation, here is another good link: MySQL and Java JDBC - Tutorial.

Moving your code to PreparedStatement, it should be like this:

PreparedStatement ps = con.prepareStatement("INSERT INTO Tiffany(Actor, Bio, More_Bio, Reason, Fact) VALUES (?, ?, ?, ?, ?) ");
ps.setString(1, Actor);
ps.setString(2, Bio);
ps.setString(3, More_Bio);
ps.setString(4, Reason);
ps.setString(5, Fact);
ps.executeUpdate();

Don't forget to close your resources after use them:

ps.close();
con.close();

score 2 · Answer 2 · answered Jul 11 '12 at 20:54

2

You need to escape your single quotes. Fortunately, Java takes care of this with PreparedStatements http://docs.oracle.com/javase/tutorial/jdbc/basics/prepared.html

answered Jul 11 '12 at 20:54

John Moses

1,283
1
12
18

Hi, I tried this but i still got the same error. String sql = "INSERT INTO Tiffany (Actor, Bio, More_Bio, Reason,Fact) values('"+Actor+"','"+Bio+"','"+More_Bio+"','"+Reason+"','"+Fact+"')"; PreparedStatement statement = con.prepareStatement(sql); statement.executeUpdate(); – user872009 Jul 11 '12 at 21:04
String sql = "INSERT INTO Tiffany (Actor, Bio, More_Bio, Reason,Fact) values('?','?','?','?','?')"; PreparedStatement ps = con.prepareStatement(sql); ps.setString(1, Actor); ps.setString(2, Bio); ... – John Moses Jul 11 '12 at 21:07
@JohnMoses please don't add bunch of code in comments, because is hard to read, it will be better and add a comment saying "I've updated my answer" or something like that. – Luiggi Mendoza Jul 11 '12 at 21:28

Database insertion

2 Answers2

Linked